Export Access Query to Excel with Formatting

Question

So I have a query in access that I want to send back to excel. While using the export wizard is fine and dandy I want to add more automation to the exporting process. So far I am working on code so during the export the final excel sheet will have some formatting. As far as basic formatting I am fine, I found many resources to help me with this.

My problem is that I want to set up conditional formatting so that if a specific column(G) has a value, then the whole row is highlighted. I am a bit lost on how to set up conditional formatting for Excel through vba code in Access

Here is what I have

Dim appExcel As Variant
 Dim MyStr As String
 Dim rng As Excel.Range

' Creates Excel object and Adds a Workbook to it
    Set appExcel = CreateObject("Excel.application")
    appExcel.Visible = False
    appExcel.Workbooks.Add


    Set wksNew = appExcel.Worksheets("Sheet1")

    appExcel.Visible = True

' The first thing I do to the worksheet is to set the font.
' Not all are required, but I included them as examples.
 With appExcel
    .Cells.Font.Name = "Calbri"
    .Cells.Font.Size = 11
    .Cells.NumberFormat = "@"                                   'all set to Text Fields
    ' My first row will contain column names, so I want to freeze it
    .Rows("2:2").Select
    .ActiveWindow.FreezePanes = True

    ' ... and I want the header row to be bold
    .Rows("1:1").Font.Bold = True
    .Rows("1:1").Font.ColorIndex = 1
    .Rows("1:1").Interior.ColorIndex = 15

    ' Adds conditional formatting based on Values in the G column

    rng = .Range("A2:J20").Select
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT($G2 = 0)"
    rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With appExcel.Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End With

Currently the code executes until my conditional formatting block and then it tells me that the Object Variable or With block is not set.

When assigning a value to an Object variable (in this case a Range) you need to use Set. Set rng = .Range("A2:J20") (drop the .Select) — Tim Williams
– Tim Williams, Commented Oct 23, 2013 at 17:37
Just a quick aside that may help: I find it useful to use the Record Macro feature in Excel. Turn it on, manually do what you want Excel to do for you - and then turn it off and review the VBA source. You can copy the VBA over to Access and use it after a few slight modifications (Creating the Excel.Application object, and prefixing constants and top level statements with the object). If you aren't super strong in Excel VBA then its a good way to clue in to all the little nuances of formatting Excel via VBA. — DHW
– DHW, Commented Oct 25, 2013 at 1:55
@DHW Thank you for the advice I actually have been doing that for a lot of formatting, it's helped learn a lot about the syntax of VBA. The problem I had was getting that to work through Access's VBA. — Kevin Malloy
– Kevin Malloy, Commented Oct 25, 2013 at 12:00

jacouh · Accepted Answer · 2013-10-23 20:05:26Z

I checked that the following code runs until the end:

Dim appExcel As Variant
 Dim MyStr As String
 Dim rng As Excel.Range
 Dim wksNew

' Creates Excel object and Adds a Workbook to it
    Set appExcel = CreateObject("Excel.application")
    appExcel.Visible = False
    appExcel.Workbooks.Add


'   Set wksNew = appExcel.Worksheets("Sheet1")
    Set wksNew = appExcel.Worksheets(1)

    appExcel.Visible = True

' The first thing I do to the worksheet is to set the font.
' Not all are required, but I included them as examples.
 With appExcel
    .Cells.Font.Name = "Calbri"
    .Cells.Font.Size = 11
    .Cells.NumberFormat = "@"                                   'all set to Text Fields
    ' My first row will contain column names, so I want to freeze it
    .Rows("2:2").Select
    .ActiveWindow.FreezePanes = True

    ' ... and I want the header row to be bold
    .Rows("1:1").Font.Bold = True
    .Rows("1:1").Font.ColorIndex = 1
    .Rows("1:1").Interior.ColorIndex = 15

    ' Adds conditional formatting based on Values in the G column

    Set rng = .Range("A2:J20")
    rng.Select
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT($G2 = 0)"
    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End With

Good luck.

Collectives™ on Stack Overflow

Export Access Query to Excel with Formatting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related