Format excel using Access 97

[el_chileno]

What is the proper syntax for manipulating Excel data from Access 97 (or is it even possible)?

I want to create an Access module that will not only export data, but reformat/manipulate the data in Excel. Here is the first part of the code I have so far:

Function export_forecast()
DoCmd.OutputTo acOutputQuery, "Forecast Maximum", acFormatXLS, , True
Dim xlwbk As Object, xlfile As Object

Set xlfile = GetObject("c:\Forecast Maximum.xls")
Set xlwbk = xlfile.Application.ActiveWorkbook

'Remove decimals
With xlwbk
Columns("E:P").Select
Selection.NumberFormat = "0"
End With
End Sub

When I run this I get a Run-Time Error 1004: Method 'Columns' of object '_Global' failed.

My guess is that xlfile.application.Activeworkbook is not the right syntax, but what do I replace it with? Nothing I have tried so far works.

I have written the VB code in Excel already and even tried to import it to the newly exported file using VBProject.VBComponents.Import but that method gave me an error "Class doesn't support Automation".

Am I going to be able to do this, or should I just export the data from Access and then run the Excel macro? This method is fine if I have to do it, but it would be nice to have it all done within Access.

 

[SydneyGeek]

Not sure of the syntax, but you can do the export as described and then run the Excel macro from Access.
Check out http://www.helenfeddema.com -- plenty of automation code and examples. You may find what you are looking for.

 

[el_chileno]

I finally figured it out. The syntax was pretty close but I learned some valuable things along the way and thought I'd post some hints here for anyone else doing this.

Here is a portion of the code that finally worked.

Function export_forecast()

'Export Forecast Maximum query to hard drive
DoCmd.OutputTo acOutputQuery, "Forecast Maximum", acFormatXLS, "c:\Forecast Maximum.xls", False

Dim xlApp As Object, xlWB As Object, xlWS As Object
' open EXCEL
Set xlApp = CreateObject("Excel.Application")
' open new forecast workbook
Set xlWB = xlApp.Workbooks.Open("C:\Forecast Maximum.xls")
' select the main worksheet and make it visible
'if you don't set it to visible it will open and run the macro in the background

Set xlWS = xlWB.Worksheets("Forecast Maximum")
xlApp.Visible = True
xlApp.Windows("Forecast Maximum.xls").Visible = True


'Manipulation code
xlApp.Cells.EntireColumn.AutoFit
xlWS.Columns("E:P").Select
xlApp.Selection.SpecialCells(xlCellTypeVisible).Select
xlApp.Selection.NumberFormat = "0"

xlWB.Save
Set xlWS = Nothing
Set xlWB = Nothing

xlApp.UserControl = True
Set xlApp = Nothing
End Function

Some keys to get this working include making sure that each call to an Excel object is qualified with the appropritate variable (Microsoft KB article 178510) And also setting all objects back to "Nothing" (Microsoft KB article 319832). If you don't do this your code will only run once and you will get run-time errors the next time you run the macro.