Run Excel Macro from Access Sub

[Tim]

Hi folks,

I built a macro in Excel called personal.xls!macro1. I
want to call it from a sub in Access. Could anyone show
me how to call it?

Thanks in advance.

Tim.

 

[Naresh Nichani MVP]

Hi:

Make a reference to Excel in your VBA project (In Tools | references select
Microsoft Excel Object Library).

You could try something like this --

Dim objExcel as Excel.Application

On Error Resume Next
Set objExcel = GetObject(,"Excel.Application")
if objExcel is Nothing then
Set objExcel = New Excel.Application
objExcel.Visible = True
end if
On Error goto 0

objExcel.Run "personal.xls!macro1" 'assumes personal.xls file is open when
Excel opens

Set objExcel = Nothing

Regards,

Naresh Nichani
Microsoft Access MVP

 

[Tim]

Naresh,

Thanks for your respone. In order to run your code
correctly, I need to open the test.xls before run the
macro. And then, I need to save the test.xls.

Could you show me how to do it?

Thanks a lots.

Tim.