How to Automate Excel from Access

[Jim]

Hi All,

I have a form that has a button on it. Behind the button is some code
(RunTheExport) that exports a table as an Excel file. I want the code
to then do some formatting of the excel file, but every time it gets
to the line ...

Dim xlApp As Excel.Application

... it blows up and says "User Defined Type not Defined." What do I
need to do to fix this to run from the button RunTheExport?

Private Sub RunTheExport_Click()

DoCmd.SetWarnings False

On Error Resume Next

...

'Export the data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"RecordsToExport", "C:\MyFile.xls", True

'**************************************************************
'FORMAT EXCEL SPREADSHEET

'declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim thruColumn As String

'initialize variables
thruColumn = "I1"

'excel application stuff
Set xlApp = New Excel.Application
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open("C:\MyFile.xls")
'For Each xlSheet In xlBook.Worksheets

'freeze panes
xlSheet.Activate
xlSheet.Range("A2", "A2").Select
xlApp.ActiveWindow.FreezePanes = True

'format header row
xlSheet.Range("A1", thruColumn).Font.Bold = True
xlSheet.Range("A1", thruColumn).Interior.ColorIndex = 15
xlSheet.Range("A1", thruColumn).WrapText = True
xlSheet.Range("A1", thruColumn).HorizontalAlignment =
xlCenter

'header row borders
xlSheet.Range("A1", thruColumn).Borders(xlLeft).Weight =
xlThin
xlSheet.Range("A1", thruColumn).Borders(xlRight).Weight =
xlThin
xlSheet.Range("A1", thruColumn).Borders(xlTop).Weight =
xlThin
xlSheet.Range("A1", thruColumn).Borders(xlBottom).Weight =
xlThin

'set row height
xlSheet.Range("A1").RowHeight = 115

'set column widths
xlSheet.Range("A1").ColumnWidth = 9
xlSheet.Range("B1").ColumnWidth = 22
xlSheet.Range("C1").ColumnWidth = 40
xlSheet.Range("D1").ColumnWidth = 15
xlSheet.Range("E1").ColumnWidth = 15
xlSheet.Range("F1").ColumnWidth = 8
xlSheet.Range("G1").ColumnWidth = 8
xlSheet.Range("H1").ColumnWidth = 8
xlSheet.Range("I1").ColumnWidth = 8

'make columns wrap
xlSheet.Range("A1", "I1").WrapText = True
xlSheet.Range("A2", "I65536").WrapText = False

'save file
xlBook.Save

'done
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

'**************************************************************
'Open Excel and run the file
Shell "EXCEL.EXE ""C:\MyFile.xls", vbNormalFocus

End Sub

[Len Robichaud]

The most likely cause is a Missing Reference.
From your Access VBA Editor go to Tools>References and check the Microsoft
Excel Object Library
Len Robichaud

"Jim" <jlre...@gmail.com> wrote in message
news:1186764379.6...@j4g2000prf.googlegroups.com...

[Jim]

Thanks Len, it worked!

[Jim]

Any idea on how to format a column in Excel with the date format? I
want it to select cell I2 to I500 and format it in the mm/dd/yy
format, but the following doesn't work.

xlSheet.Range("I2", "I500").Select
xlSheet.Range("I2", "I500").Format = "mm/dd/yy"

[Len Robichaud]

"Jim" <jlre...@gmail.com> wrote in message

news:1186776675.2...@m37g2000prh.googlegroups.com...

xlSheet.Selection.NumberFormat = "mm/dd/yy;@"
Here is a tip. Use the Macro recorder in Excel to "record" the steps you
would take to do this manually. Stop recording when finished. Then go to
Tools>Macros>edit and you can see the VBA code. I still use this method as
it's faster then the help menu.

Len

[Jim]

On Aug 10, 3:48 pm, "Len Robichaud" <len.robich...@rqwproserv.com>
wrote:
> "Jim" <jlrehm...@gmail.com> wrote in message

Dude... You ROCK! Great idea... thanks!