Make Excel code execute after TransferSpreadsheet

[shauntwo]

Hi,

I have a form in Access that transfers a query using the DoCmd.TransferSpreadsheet method to a predefined Excel spreadsheet. After the DoCmd, I then define an Excel application object to open the workbook automatically that received the transfer. Within the workbook, I have an Autpen macro that I want to run whenever the workbook opens. Problem is, the Excel macro doesn't automatically execute; I'm guessing that the dailog box that would otherwise come up during the opening of a workbook, that asks users whether or not they want to enable macros, is automatically choosing to disable my macro. What can I do to get around this?

Thanks for your help.

Shaun

 

[MadForIt]

Hi Shaun,

As far as i'm aware Autpen macros do not run automatically if the files are opened by VB code.

I had a similar problem using VB with Excel to open another spreadsheet which had an Autpen macro. This also didn't Auto run.

To get around this i had to use the RunAutoMacros command immediately after opening the file.

Workbooks.Open ("c:\Temp\Test.xls&quot
ActiveWorkbook.RunAutoMacros xlAutoOpen

Maybe you need to do something similar in your Excel Application Object.

Regards