Run macros in Excel from Access button

[jenvandiver]

I've coded a form button in Access to open an Excel template, which is supposed to automatically run a query for data on open, however when it opens using the button in Access, it doesn't automatically refresh the data and I have to manually do it. It works perfect if I double click directly on the template, but opening it through Access seems to disable the aumatic refresh.

So, at this point I'm trying to come up with language to add to the form button that will automatically run 2 macros (created in Excel) immediately after the form is opened. mcr1 is named RefreshData and mcr2 is AutoFitRows. Here is the code I have so far...(of course it isn't working!)

with oApp
.Workbooks.Open "C:\Documents and Settings\vandiverj\AMTPT\Training Tool\Templates\Comments Matrix.xlt"

ActiveWorkbook.RunAutoMacros xlRefreshData

With ActiveWorkbook
.RunAutoMacros xlRefreshData

End With


Thanks in advance for any help on this...

 

[Philem]

Hullo. Should be easy enough. Take a look here to get you started. As for the Excel part of your question, have the first macro make a Call to the second macro.

HTH

P

 

[jenvandiver]

Thanks for the help. The macros I need to run automatically when the Excel workbook opens are macros within that document. The code I've seen so far seems to be running Access macros to call data for Excel. I've got the properties for my Excel template set to automatically run it's macros on open, which it does fine if I double click directly on the tempate file. But when open it through Access, the Excel macros don't work automatically.

I'll keep plugging away at it. Thanks for pointing me in the right direction!