Excel Automation (Access 97 & Excel 97)

Topic

New Reply

Hope someone can help…

I have created an Excel macro (that is currently run from a custom excel button), but I would like to run the macro from Access.

I have used the following code (where ‘Format_Financial_Report’ is the name of the macro and strFileName is the name of the excel document):

Set XL = CreateObject(“Excel.Application”)
With XL.Application
.workbooks.Open strFileName
.Visible = True
.Run “Format_Financial_Report”
.Quit
End With
Set XL = Nothing

The Macro is saved to my ‘Personal Macro Workbook’ so that it is always available for the button, irrespective of what workbooks are open. The problem is that the above code fails to find the macro, because the ‘Personal.xls’ VBAPRoject isn’t visible in the code windows until after you’ve run the macro once.

Any ideas how to ‘call’ the macro from ‘Personal Macro Workbook’ ? or failing that, is there a way for me to ‘call’ the excel button rather than the macro (the button is always visible)

I’ve looked through the posts to date, as well as the MS support pages and couldn’t find anything.

Yours hopefully

Reply | Quote

Replies

Chris,

You have to open the workbook witch contain the macro also.
Something like this :

Set XL = CreateObject(“Excel.Application”)
With XL.Application
.workbooks.Open “c:Program FilesMicrosoft OfficeOfficeXlStartPersonal.xls”
.workbooks.Open strFileName
.Visible = True
.Run (“Personal.xls!Format_Financial_Report”)
.Quit
End With
Set XL = Nothing

Hope this help

Reply | Quote

Hi Francios,

this worked a treat…

(the only thing I had to change was taking the ‘.Visible=True’ line out – as the code was creating 9 different spreadsheets and running the macro on each of them, it created too many annoying flashing screens)

Many many thanks

Chris D

Reply | Quote