Excel Reports from Acess

[WillaimSmith]

I need to open an excel report from an MS ACCESS(97) application and a I want a macro(for formatting) to run automatically when the excel file opens.

Can some one in the forum please tell me how to do this?

Any help will be appreciated.

William

 

[PaulBricker]

To open the Excel file, you can use the Shell method.

Dim retval
retval = Shell("C:\Program Files\Microsoft Office\Office\Excel.exe C:\MyStuff\SchooldudeFlowChart.xls")

As for the macro to format, I would assume that you would put the macro directly in the Excel file. I can't tell you how to do that. You would probably need to go to the Office forum, Forum68 to get what you need there.

Paul

 

[batteam]

There is a way to run a macro in Excel when the spreadsheet opens. To do this, right click on the blue top bar of the spreadsheet (same bar the name of the spreadsheet is on), the choose 'View code'then in the left hand box, choose 'Workbook'. You should then see:

Private Sub Workbook_Open()

End Sub

Just enter your name of the macro(s) - in your case, a macro(s) that will format your sheet - in this Private Sub. They will run automatically each time your spreadsheet is opened. Works great. Hope this helps, good luck.

 

[muralisai25]

Hi William,
Lets say you want to run a macro everytime file Test.xls is opened. First record a Macro to do the things you want to perform like formatting etc etc within the test.xls. Then within that test.xls rename the macro to Auto_open by Clicking on tools>macro>macro and clickon edit button, change the sub to Sub Auto_open(). So everytime you open this file test.xls, the macro will be excuted. This will stay local to ONLY that excel file. But if you want to run for any excel files then you can save the macro in the Xlator folder under the ms office setup directory.
Hope this helps
Sai