Open Excel and run macro from Access

[MikeCDPQ]

I would like to have an Access script that would open an Excel workbook and call a script within excel.

I've tried different things that I saw here on the site but nothing seems to work.

Any suggestions anyone ?

Thanks for any help.

 

[TonyJollans]

Hi MikeMcKesson,

This should do it ..

Dim appExcel As New Excel.Application
appExcel.Workbooks.Open

"C:\YourPath\YourWorkbook.xls"

appexcel.Run

"YourMacroInExcel"

appExcel.Quit
set appexcel = Nothing

You will need to have a reference to the Excel Object Library, and your Excel code in a Module in the Workbook you are opening.

Enjoy,
Tony

 

[MikeCDPQ]

Hi Tony,

Well, not having too much luck. The very first line (Dim appExcel As New Excel.Application)

produces following error message:

Compile error:
User-defined type not defined

Really not overly familiar with VB yet so I don't quite know what that means. Any suggestions ?

Thanks a lot for your help.

Mike

 

[zollo9999]

Mike

You are writing your VBA in an Access Module right!!!

As Tony suggested, do this FIRST:

"You will need to have a reference to the Excel Object Library, in [the Access] Module." (not just the Worksheet as Tony said)

To do this,
Open your Access VBA code Module,
From the "Tools" Menu, Select References...
Scroll through the available refereces to find:
[] Microsoft Excel X.x Object Library
Click the checkbox then click Ok

Then Access should be able to understand the first line of the code.

(I'm not sure if you should restart access to ensure the reference is recognized, but I think not).



Zollo9999 A+
(Very Part-Time Programmer)