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

 

[ranman256]

Make sure you have the Excel reference checked. VBE menu, TOOLS, REFERENCES.
Checkmark EXCEL.

 

[Joe Hanna]

Which reference would you use specifically?

 

[Joe Hanna]

I did find another code that seem to work until it gets to the Ms Excel Macro VBA.
The Code is:

The error appear at line "Sheets("Worksheet").Select",
just at where the Formatting Excel MACRO suppose to start.

The error reads: "Compile Error: Sub or Function not defined"

Any idea how to fix it?

Dim xl As Object
 
'Step 1:  Start Excel, then open the target workbook.
   Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("fPath")
 
'Step 2:  Make Excel visible
   xl.Visible = True

[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


[B]'===End Formatting Excel MACRO===[/B]
           'End With
'Step 4:  Close and save the workbook, then close Excel
   xl.ActiveWorkbook.Close (True)
    xl.Quit
 
'Step 5:  Memory Clean up.
   Set xl = Nothing

 

[Norie]

You should get rid of Selection, it means nothing in Access VBA, you also don't need it in Excel VBA.

Also, use the workbook and worksheet references you create at the start of the code.

Try this for the Excel formatting part.

 Dim filePath As String
    Dim xl As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    filePath = "c:\mypath"
    
    Set xl = New Excel.Application

    Set xlBook = xl.Workbooks.Open(filePath)

    Set xlSheet = xlBook.Worksheets(1)

    xl.Visible = True

    '===Formatting Excel MACRO===

    With Sheets("Worksheet")
        .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        
        With .Range("A1:L1")
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = -0.499984740745262
                .PatternTintAndShade = 0
            End With
            With .Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End With
        
        .Range("E1").Value = "USABB"
        
        With .Range("M1:W1")
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = -0.499984740745262
                .PatternTintAndShade = 0
            End With
            With .Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End With

        .Range("Q1").FormulaR1C1 = "Vendor"

        xl.Goto .Rows(2), True

        xl.Goto .Range("A1")
        '===End Formatting Excel MACRO===
        'End With
    End With


    'Save
    xlBook.Save

    xl.Quit
    
    Set xlApp = Nothing
    Set xlSheet = Nothing

 

[Joe Hanna]

Hi Norie,

I tried it. It produced an error in line:

xl As Excel.Application

The error reads: "Compile Error: User-defined type not defined"

When I go to Visual Basic > Tools > References, I have all of these references chacked:
Microsoft Office 11 Object library
Microsoft Office 12 Object library
Microsoft Office 15 Object library
Microsoft DAO 3.6 Object library
Visual Basic Applications
OLE Automation
Microsoft ActiveX Data Objects 3.1 Library
Microsoft XML, v6.0

Do you think I should have anything else chacked in order for this code to run?
I have Access 2003 and the Export is to .xls, MS Excel 97-2003 file format.

Thanks!

 

[Norie]

Yes you should have a reference to Microsoft Excel x.0 Object library, where x.0 is the version number.

 

[Joe Hanna]

Thank you so much. The excel is now opening at least.

However, the Macro code copied from Excel produces errors on Insert below, for example. I am sure that this will not be the only error. In your experience, is there a way to paste VB code Access after it had been recorded in Excel?

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

What it does is it inserts a row.

 

[Norie]

Did you try the code I posted?

As for just pasting Excel recorded code, that's not going to work.

Half, or more, of the stuff in the recorded code won't be recognised in Access VBA so either won't work properly or causes errors.

 

[Joe Hanna]

Hi Norie,
i got it. Per your explanation in the first post, I suppose i have to remove all Selection/Select references and all ActiveCell as well.
Is there any way to mark best answer here?