Still need assistance, macro trouble

[Guest]

Good afternoon everybody,

I am working on an Access database that exports data from an Access query to
an Excel spreadsheet. I then need to run an Excel macro on that spreadsheet.

Ken provided some cool code to try and I got export to work and I can see
Excel open and I can see exported data in an Excel spreadsheet fine. But code
hangs when tries to run the Excel macro and flags line of code as yellow that
calls Excel macro. I made sure I got syntax correct, but.....

I also noticed If I open a standard excel spreadsheet alone and click on my
macros menu, I can see a list of macros, but when a spreadsheet is exported
from Access and automatically opened, can see my exported data, but my list
of macros is blank and seems unavailable. Anybody know why? (I know Excel
macros get stored in personal.xls file so they can be used on any workbook.
But do imported files not have access to this?) They can't be stored locally
to a workbook because every time data is exported, the file is overwritten.
Any suggestions?

 

[MacDermott]

Try opening your personal.xls file programmatically:
XL.workbooks.open("personal.xls")
Of course you'll use your own variable for the XL Application, and you'll
probably need to include the path to personal.xls.

HTH
- Turtle

 

[Ken Snell [MVP]]

What error message are you getting when your code "errors"?

The code that I provided assumes that the macro is in the workbook file that
you're opening. If the macro is in your personal workbook, then you'll need
to change the code line that runs the macro to use the path and file name of
the personal macro book instead of the workbook that has the data.