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"
The VBA looks like:
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"
Code:
Dim xl As Excel.Application
Code:
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