Running Excel Macro from Access

[Mike-El Paso]

I need help on running an Excel Macro from within Access. I am able to open Excel from Access using RunApp but not able to run the Macro I need to further automate my procedure. Any help would be greatly appreciated.

Thanks
Mike

 

[kman]

If you have Northwind.mdb it has an example of how to export your reports into excel format via a macro/vb code. I can provide an example if that will help but you will have to provide email address since you can not post attachments in the message board.

Why not run the macro/vb code from excel I have created a lot of this functionality in excel and it works quite well interacting with Microsoft Access. Again if you need some examples I would be happy to send them to you or post the code if it applies to what your trying to do.

Let me know if I can be of further assistance

Kman

 

[Mike-El Paso]

KMan
I have everything set up in Access and trying to make things easy for my boss to run reports when I'm not there. I have everything you click a button on a user from in Access and the data is extracted from the mainframe (server) saved in an Access table and a text file is exported to my C:drive. I than have Access start up a Program called Monarch and it takes the text file and applies a model to it and export it out in an Excel file format. I have another file in Excel that imports the data into the spreadsheet and will print out the report from Excel. I am able to start Excel from within Access by using the RunApp procedure but I do not know how to call up the Excel macro to run my files from Access. Any further help would be great. My email address is mhunnicu@elp.rr.com

Thanks
Mike

 

[Corticus]

duplicate thread, see:
http://216.92.17.166/board/viewtopic.php?topic=34086&forum=14&2

 

[Russell Hauf]

You can do something like this. You must first go to Tools-References, and check the box next to Microsoft Excel X.x Object Library.

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Compare</font> Database
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<font color='#000080'>Sub</font> RunXlMacro()
<font color='#000080'>Dim</font> appXL <font color='#000080'>As</font> Excel.Application
<font color='#000080'>Dim</font> wbk <font color='#000080'>As</font> Excel.Workbook

<font color='#000080'>Set</font> appXL = New Excel.Application

<font color='#000080'>Set</font> wbk = appXL.Workbooks.Open("D:DeleteMe.xls")

appXL.Run wbk.Name & "!Test"
wbk.Close False
appXL.Quit

<font color='#000080'>Set</font> appXL = Nothing

<font color='#000080'>End</font> <font color='#000080'>Sub</font></font></pre>

This code will run a macro named "Test" in the workbook named "DeleteMe.xls". If you want to show Excel (which you probably do if you are using RunApp), then you should put in a line like the following after the Set appXL = New Excel.Application line:

appXL.Visible = True

The wbk.Close False line closes the workbook w/o saving changes - modify this if you want to save any changes that your macro may make.

Hope this helps,

Russell