Format Excel spreadsheet from Access - VBA

[Joe Hanna]

Hello,
I have a VBA code in Access 2003 that exports sequence of files.
I am trying to find a way to format these Excel files from Access once they are exported. The way I approach it is, I enter a VBA code to format the Excel file once the file is exported and before the next file is exported.
I have a prerecorded macro in excel that I would like to paste in the VBA.


It gives me error in this line: error type "User-defined type not defined"

Dim xl As Excel.Application

The VBA looks like:

DoCmd.TransferSpreadsheet acExport, , "Worksheet", fPath, False
[B]'===Formatting VBA===[/B]
Application.SetOption "Show Status Bar", True
 
Dim filePath As String
filePath = "c:\mypath"
 
Dim xl As Excel.Application
Dim xlBook As Excel.workbook
Dim xlSheet As Excel.worksheet
 
Set xl = New Excel.Application
 
Set xlBook = xl.Workbooks.Open(filePath)
 
Set xlSheet = xlBook.Worksheets(1)
 
xl.Visible = True
 
With xlSheet
 
[B]'===Formatting Excel MACRO===[/B]
 
    Sheets("Worksheet").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1:L1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "USABB"
    Range("M1:W1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Vendor"
    Rows("2:2").Select
    Range("A1").Select
[B]'===End Formatting Excel MACRO===[/B]
           'End With
 
    xlApp.Visible = True
           
    'Save
    .Application.ActiveWorkbook.Save
    End With
 
    Set xlApp = Nothing
    Set xlSheet = Nothing
 
 
[B]'===End Formatting VBA===[/B]
Next i

 

[Joe Hanna]

It did work. Thanks again Norie!
I also made part of the Excel Code work as well.
One think that i do not understand is how we reference different worksheets in excel to be formatted.
For exaple if I export two querires to Excel on two different tabs, the first tab has name Q1 and second Q2.
What would be the code to switch to Q1, format it, then switch to Q2 tab and format it as well?

 

[Norie]

In the code I posted previously you would change this so it references whichever sheet you want to format.

 With xlBook.Sheets("Worksheet")

So for Q2 it would become this.

With xlBook.Sheets("Q2")

PS Note I've added xlBook to the above code.

When automating Excel or any other application, you should make sure you reference everything properly.