INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formatting Access appended fields in Excel

Formatting Access appended fields in Excel

(OP)
I have the following code that I use to append to an Excel worksheet with an Access table
but I would also like to set the appended columns to BOLD text, center the text in each field and set the text color to BLUE.
Where can I find some examples on how to do this?

Thanks

CODE -->

Dim strQuery As String
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim lngLastRow As Long
Dim db As DAO.Database
Dim rs1 As DAO.Recordset

   strQuery = "Excel_qry"  
   DoCmd.OpenQuery strQuery, acViewNormal, acReadOnly
     
    Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Excel_tbl", dbOpenSnapshot)
    
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open("C:\MyStuff.xlsx")
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets("Personal")         'RSP
        objWkb.Worksheets("RSP").Activate
'        objWkb.Windows("RSP").Visible = True

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
    End With
    
    Set rs1 = Nothing 

RE: Formatting Access appended fields in Excel

There is vba forum707: VBA Visual Basic for Applications (Microsoft) with mostly non-access ms office topics. A lot of stuff you can find in FAQs section. Self learning: open excel, display VBE and object browser, see Range object properties and (help file/site) referencing.

combo

RE: Formatting Access appended fields in Excel

(OP)
Thanks for the link Combo

RE: Formatting Access appended fields in Excel

VBA forum that combo mentioned is a good place to ask these questions, but you can start by recording a macro in Excel:
Select rows that you want to change, align them center, and make them blue.

You will eventually end up with a code something like this (eliminating all not needed pieces)

CODE

With Rows("10:24")
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .Font.Color = vbBlue
End With 
Then you need to replace hard-coded 10 with your lngLastRow, and hard-coded 24 with something like lngLastRow + rs1.RecordCount

Code not tested:

CODE

With objSht
    .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
    With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Font.Color = vbBlue
    End With
End With 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Formatting Access appended fields in Excel

(OP)
Thanks Andy


The Code that you posted places the text as Bold and Blue but does not center any of the fields:

CODE

With objSht
    .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
    With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Font.Color = vbBlue
    End With
End With 

RE: Formatting Access appended fields in Excel

(OP)
One other related question...

How do I match the date format for the items that I append in the A column with the
Long date format of Month day, Year that is currently in the spreadsheet?

I am guessing that it would be similar to the following but I am not sure how to apply this
format to the first column of what I append...

CODE -->

' .NumberFormat = "mmmm d, yyyy" 

thanks

RE: Formatting Access appended fields in Excel

Well, works in my Excel 2013:
.HorizontalAlignment = xlCenter


As far as changing the date format, macro recorder in Excel is your friend:
.Columns("A:A").NumberFormat = "mmmm dd, yyyy"


Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Formatting Access appended fields in Excel

In excel xlCenter=-4108, use the number instead (as in Find in your code) for late binding or link to excel library to have access to excel named constants, otherwise you assign 0.

combo

RE: Formatting Access appended fields in Excel

(OP)
Thanks Combo-

RE: Formatting Access appended fields in Excel

(OP)

Andy Wrote:

Quote:


As far as changing the date format, macro recorder in Excel is your friend:
.Columns("A:A").NumberFormat = "mmmm dd, yyyy"

This date format does not work for me. Not sure if it is this old version of Excel from
Office 2010 (This is the version that the company has made available)

Here is how I have it set up in my code. Am I doing something wrong?

CODE -->

With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
            With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
'                .Font.Bold = True
                .HorizontalAlignment = -4108
                .Font.Color = vbBlue
                .Font.Name = "Arial"
                .Font.Size = 12
            End With
            With .Columns("A:A").NumberFormat = "mmmm dd, yyyy"
            End With
        End With 

Thanks again

RE: Formatting Access appended fields in Excel

>This date format does not work for me.
Well, what does it do? Errors out? Crashes? Complains thru the speakers? smile

.Columns("A:A").NumberFormat = "mmmm dd, yyyy"
May or may not work depending on what data you have in column A

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Formatting Access appended fields in Excel

(OP)
Something else that is very subtle is that the following code is also changing the color of the text in the row above the
data that I append into the worksheet.

This is not a desired condition as I only want to highlight what was dded and not the last line of what was already in the
worksheet.

CODE -->

lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
                            
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
        
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
            With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
'                .Font.Bold = True
                .HorizontalAlignment = -4108
                .Font.Color = vbBlue
                .Font.Name = "Arial"
                .Font.Size = 12
            End With 

I tried changing the line (highlighted in red) as follows, thinking that it would start the formatting on the next line
(on the data that I appended) but found that doing this didn't seem to matter at all:

CODE -->

With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
            With .Rows(lngLastRow + 1 & ":" & lngLastRow + rs1.RecordCount) 

Any suggestions on how I might fix this issue?

Thanks again

RE: Formatting Access appended fields in Excel

(OP)
Andy wrote:

Quote:


>This date format does not work for me.
Well, what does it do? Errors out? Crashes? Complains thru the speakers? smile

.Columns("A:A").NumberFormat = "mmmm dd, yyyy"
May or may not work depending on what data you have in column A

I am pulling the date from a spreadsheet that is in the MM/DD/YY format
and this is read into my Access tables and then this same date format that is
appended to my other worksheet.

RE: Formatting Access appended fields in Excel

You paste the recordset starting from lngLastRow+1 row, it should be coloured if you reference new data rows with With .Rows(lngLastRow + 1 & ":" & lngLastRow + rs1.RecordCount). If not, test if you get proper lngLastRow or old last row is already coloured.

In your recent code you copy the recordset twice, the first With...End With can be deleted.

Concerning date formats, you may do some tests with copied data in excel. Check if you have somewhere small green dots in cells' corner. In this situation this may indicate that those data was pasted astext, number formats will not work. Additional test for dates: select range and change format to general, real dates should be displayed as numbers. If not, again, date is stored as text and date cannot be formatted.

combo

RE: Formatting Access appended fields in Excel

If the lngLastRow is 20, your CopyFromRecordset dumps the data from your rs1 starting in row 21 (lngLastRow + 1)

So your code:

CODE

With .Rows(lngLastRow + 1 & ":" & lngLastRow + 1 + rs1.RecordCount) 
Should be dealing with rows 21 up to whatever the number is for lngLastRow + 1 + rs1.RecordCount

When you run your code and you stop at CopyFromRecordset line, what are the values for lngLastRow and rs1.RecordCount? And do they correcpond to the rows in Excel you want to re-format?

Quote (irethedo )


I am pulling the date from a spreadsheet that is in the MM/DD/YY format and this is read into my Access tables and then this same date format that is appended to my other worksheet.

Shouldn't that be:
I am pulling the date from to a spreadsheet that is in the MM/DD/YY format and this is read into from my Access tables and then this same date format that is appended to my other worksheet ( ???)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Formatting Access appended fields in Excel

(OP)
Combo wrote:

Quote:


You paste the recordset starting from lngLastRow+1 row, it should be coloured if you reference new data rows with With .Rows(lngLastRow + 1 & ":" & lngLastRow + rs1.RecordCount). If not, test if you get proper lngLastRow or old last row is already coloured.

In your recent code you copy the recordset twice, the first With...End With can be deleted.

The following code fixed the lngLastRow issue where I added lngLastRow = lngLastRow + 1:

CODE -->

lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        End With
         lngLastRow = lngLastRow + 1
        With objSht
            .Range("A" & lngLastRow).CopyFromRecordset rs1
            With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
'                .Font.Bold = True
                .HorizontalAlignment = -4108
                .Font.Color = vbBlue
                .Font.Name = "Arial"
                .Font.Size = 12
            End With 

Combo Wrote:

Quote:


Concerning date formats, you may do some tests with copied data in excel. Check if you have somewhere small green dots in cells' corner. In this situation this may indicate that those data was pasted astext, number formats will not work. Additional test for dates: select range and change format to general, real dates should be displayed as numbers. If not, again, date is stored as text and date cannot be formatted.

I just noticed that green dot that is in the spreadsheet from which I pull the date from into my Access table. This spreadsheet is created from a query from an Oracle EBS system.

Thanks for setting me straight on that.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close