add vba code to report using analyze it with excel function

[Pete]

Hi Experts

Having difficulty in doing the following task in MS Access. When i click the report "TurnaroundReport_by_PlanName" in MS Access i can send the report in MS Excel using the Analyze it with MS Excel function and the informaton opens up in excel....

However, i would like to take this process a step further by adding vba code to the resultant excel document that is opened up and run a macro on open excel.....

in one sequence.

[CreganTur]

Using the Analyze It button is the manual way to send a report to Excel. If you want to automate this, then look into the TransferSpreadsheet method- Access Help has good resources.

In order to open the spreadsheet after you Transfer it out, you would use:
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr(34) _
& FileName & Chr(34),1")

In the above you are setting a reference to Excel, declaring that it is the application you want to utilize. Then you use Chr(34) to wrap your variable FileName (value of which is the full filepath to your spreadsheet) in double quotes- this is required as it is needed to tell Shell that you want to open the declared filepath with Excel application.

The '1' is part of the WindowStyle parameter, which determins how the spreadsheet looks when it opens. Using 1 sets the window to normal focus- window is normal size and has focus.


However, i would like to take this process a step further by adding vba code to the resultant excel document that is opened up and run a macro on open excel

You can't use automation to put code into a workbook. But you can set a reference in Access to the Microsoft Excel object library and use this to run code against your new spreadsheet- the code is kept in Access, it just runs against the spreadsheet.