S
SimonN
From Access I am extracting data out of an Excel workbook and also running
some macros in this workbook. I am using the "GetObject" command to trigger
excel so I don't have to worry if it is already open or not. I have found
that I can only call one macro in excel. After that I get a Run-time error
1004 - The macro cannot be found. I got around this by creating another
macro in excel which calls all the macros I need. However, I have since put
Workbook_open macro into the Excel workbook. Now when the workbook is
triggered this runs and as a result I can't call the other macro.
Is this a restriction that you can only call one excel macro? Is there a
way of getting around it?
Can I prevent the Workbook_open macro from running when the workbook is
opened from access?
A nice to have on the side is, Can I get rid of the Enable Macros dialouge
box coming up when Excel is opened through automation?
I have included a simplified version of my access code below. Any help
would be massively appreciated. I haven't found this documented anywhere
yet, books or online.
Dim AppXL As Excel.Application
Dim BookXL As Excel.Workbook
Set BookXL = GetObject("C:\testSS.xls", "excel.application")
Set AppXL = BookXL.Parent
AppXL.Run "testmacro" 'is first macro in workbook
'AppXL.Run "testmacro2" 'is second macro in workbook
AppXL.Windows("testSS.xls").Visible = True
BookXL.Save
AppXL.Quit
Set AppXL = Nothing
End Sub
some macros in this workbook. I am using the "GetObject" command to trigger
excel so I don't have to worry if it is already open or not. I have found
that I can only call one macro in excel. After that I get a Run-time error
1004 - The macro cannot be found. I got around this by creating another
macro in excel which calls all the macros I need. However, I have since put
Workbook_open macro into the Excel workbook. Now when the workbook is
triggered this runs and as a result I can't call the other macro.
Is this a restriction that you can only call one excel macro? Is there a
way of getting around it?
Can I prevent the Workbook_open macro from running when the workbook is
opened from access?
A nice to have on the side is, Can I get rid of the Enable Macros dialouge
box coming up when Excel is opened through automation?
I have included a simplified version of my access code below. Any help
would be massively appreciated. I haven't found this documented anywhere
yet, books or online.
Dim AppXL As Excel.Application
Dim BookXL As Excel.Workbook
Set BookXL = GetObject("C:\testSS.xls", "excel.application")
Set AppXL = BookXL.Parent
AppXL.Run "testmacro" 'is first macro in workbook
'AppXL.Run "testmacro2" 'is second macro in workbook
AppXL.Windows("testSS.xls").Visible = True
BookXL.Save
AppXL.Quit
Set AppXL = Nothing
End Sub