Formatting Access appended fields in Excel 2

[irethedo]

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

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

 

[combo]

There is vba forum707 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

 

[irethedo]

Thanks for the link Combo

 

[Andrzejek]

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)

With Rows("10:24")
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .Font.Color = vbBlue
End With

Then you need to replace hard-coded 10 with your [tt]lngLastRow[/tt], and hard-coded 24 with something like [tt]lngLastRow + rs1.RecordCount[/tt]

Code not tested:

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Thanks Andy


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

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

 

[irethedo]

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...

   ' .NumberFormat = "mmmm d, yyyy"

thanks

 

[Andrzejek]

Well, works in my Excel 2013:[tt]
.HorizontalAlignment = xlCenter[/tt]

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



Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[combo]

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

 

[irethedo]

Thanks Combo-

 

[irethedo]

Andy Wrote:

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?

        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

 

[Andrzejek]

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

[tt].Columns("A:A").NumberFormat = "mmmm dd, yyyy"[/tt]
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.

 

[irethedo]

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.

     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:

       With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
            With .Rows(lngLastRow [b][COLOR=#EF2929]+ 1[/color][/b] & ":" & lngLastRow + rs1.RecordCount)

Any suggestions on how I might fix this issue?

Thanks again

 

[irethedo]

Andy wrote:

>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.

 

[combo]

You paste the recordset starting from lngLastRow+1 row, it should be coloured if you reference new data rows with [tt]With .Rows(lngLastRow + 1 & ":" & lngLastRow + rs1.RecordCount)[/tt]. 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 [tt]With...End With[/tt] 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

 

[Andrzejek]

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

So your code:

With .Rows(lngLastRow[red] + 1 [/red]& ":" & lngLastRow[red] + 1 [/red]+ rs1.RecordCount)

Should be dealing with rows 21 up to whatever the number is for [tt]lngLastRow + 1 + rs1.RecordCount[/tt]

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

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.

 

[irethedo]

Combo wrote:

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:

       lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        End With
        [COLOR=#EF2929] lngLastRow = lngLastRow + 1[/color]
        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:

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.