|
The myworkbook.xls Auto_Open() macro won't run if started by a VBS
I've created an example.VBS file that contains:
----------------- Dim XLApp Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" ----------------- In the myworkbook.xls is an auto run macro ------------- Sub Auto_Open() 'some code End Sub ------------- If I start the myworkbook.xls by itself it runs the Auto_Open() macro *just fine*. However, when my myworkbook.xls is called via the example.VBS script it DOES NOT auto run the macro. Any clues? |
The myworkbook.xls Auto_Open() macro won't run if started by a VBS
That is by design. The help file states that you have to use the
RunAutoMacros, like this: xlapp.ActiveWorkbook.RunAutoMacros 1 'xlAutoOpen from the help file: Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros. expression.RunAutoMacros(Which) expression Required. An expression that returns one of the objects in the Applies To list. Which Required XlRunAutoMacro. XlRunAutoMacro can be one of these XlRunAutoMacro constants. xlAutoActivate. Auto_Activate macros xlAutoClose. Auto_Close macros xlAutoDeactivate. Auto_Deactivate macros xlAutoOpen. Auto_Open macros Example This example opens the workbook Analysis.xls and then runs its Auto_Open macro. Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen This example runs the Auto_Close macro for the active workbook and then closes the workbook. With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With -- Regards, Juan Pablo González Excel MVP "CRayF" wrote in message ... I've created an example.VBS file that contains: ----------------- Dim XLApp Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" ----------------- In the myworkbook.xls is an auto run macro ------------- Sub Auto_Open() 'some code End Sub ------------- If I start the myworkbook.xls by itself it runs the Auto_Open() macro *just fine*. However, when my myworkbook.xls is called via the example.VBS script it DOES NOT auto run the macro. Any clues? |
The myworkbook.xls Auto_Open() macro won't run if started by a
I apologize. Im struggling a little with the syntax. I augmented the
example.VBS file and added the ActiveWorkbook.RunAutoMacros xlAutoOpen that I did not previously have there and now receive the error below€¦ Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.Visible = true xlapp.Workbooks.Open "myworkbook.xls" xlapp.ActiveWorkbook.RunAutoMacros xlAutoOpen ERROR: C:\MyDocuments\XLS\example.VBS Line 6 Char: 1 Error RunAutoMacros method of Workbook class failed Code: 800A03EC Source: Microsoft Excel "Juan Pablo González" wrote: That is by design. The help file states that you have to use the RunAutoMacros, like this: xlapp.ActiveWorkbook.RunAutoMacros 1 'xlAutoOpen from the help file: Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros. expression.RunAutoMacros(Which) expression Required. An expression that returns one of the objects in the Applies To list. Which Required XlRunAutoMacro. XlRunAutoMacro can be one of these XlRunAutoMacro constants. xlAutoActivate. Auto_Activate macros xlAutoClose. Auto_Close macros xlAutoDeactivate. Auto_Deactivate macros xlAutoOpen. Auto_Open macros Example This example opens the workbook Analysis.xls and then runs its Auto_Open macro. Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen This example runs the Auto_Close macro for the active workbook and then closes the workbook. With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With -- Regards, Juan Pablo González Excel MVP "CRayF" wrote in message ... I've created an example.VBS file that contains: ----------------- Dim XLApp Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" ----------------- In the myworkbook.xls is an auto run macro ------------- Sub Auto_Open() 'some code End Sub ------------- If I start the myworkbook.xls by itself it runs the Auto_Open() macro *just fine*. However, when my myworkbook.xls is called via the example.VBS script it DOES NOT auto run the macro. Any clues? |
The myworkbook.xls Auto_Open() macro won't run if started by a
If you look at the code I posted, you'll notice that I used the numeric
value of the constant (1), instead of xlAutoOpen. Try it like that... -- Regards, Juan Pablo González Excel MVP "CRayF" wrote in message ... I apologize. I'm struggling a little with the syntax. I augmented the example.VBS file and added the ActiveWorkbook.RunAutoMacros xlAutoOpen that I did not previously have there and now receive the error below. Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.Visible = true xlapp.Workbooks.Open "myworkbook.xls" xlapp.ActiveWorkbook.RunAutoMacros xlAutoOpen ERROR: C:\MyDocuments\XLS\example.VBS Line 6 Char: 1 Error RunAutoMacros method of Workbook class failed Code: 800A03EC Source: Microsoft Excel "Juan Pablo González" wrote: That is by design. The help file states that you have to use the RunAutoMacros, like this: xlapp.ActiveWorkbook.RunAutoMacros 1 'xlAutoOpen from the help file: Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros. expression.RunAutoMacros(Which) expression Required. An expression that returns one of the objects in the Applies To list. Which Required XlRunAutoMacro. XlRunAutoMacro can be one of these XlRunAutoMacro constants. xlAutoActivate. Auto_Activate macros xlAutoClose. Auto_Close macros xlAutoDeactivate. Auto_Deactivate macros xlAutoOpen. Auto_Open macros Example This example opens the workbook Analysis.xls and then runs its Auto_Open macro. Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen This example runs the Auto_Close macro for the active workbook and then closes the workbook. With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With -- Regards, Juan Pablo González Excel MVP "CRayF" wrote in message ... I've created an example.VBS file that contains: ----------------- Dim XLApp Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" ----------------- In the myworkbook.xls is an auto run macro ------------- Sub Auto_Open() 'some code End Sub ------------- If I start the myworkbook.xls by itself it runs the Auto_Open() macro *just fine*. However, when my myworkbook.xls is called via the example.VBS script it DOES NOT auto run the macro. Any clues? |
The myworkbook.xls Auto_Open() macro won't run if started by a
xlAutoOpen is a constant defined in Excel which your vbs code has no
access to. Use 1 instead as Juan Pablo posted. Tim "CRayF" wrote in message ... I apologize. I'm struggling a little with the syntax. I augmented the example.VBS file and added the ActiveWorkbook.RunAutoMacros xlAutoOpen that I did not previously have there and now receive the error below. Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.Visible = true xlapp.Workbooks.Open "myworkbook.xls" xlapp.ActiveWorkbook.RunAutoMacros xlAutoOpen ERROR: C:\MyDocuments\XLS\example.VBS Line 6 Char: 1 Error RunAutoMacros method of Workbook class failed Code: 800A03EC Source: Microsoft Excel "Juan Pablo González" wrote: That is by design. The help file states that you have to use the RunAutoMacros, like this: xlapp.ActiveWorkbook.RunAutoMacros 1 'xlAutoOpen from the help file: Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros. expression.RunAutoMacros(Which) expression Required. An expression that returns one of the objects in the Applies To list. Which Required XlRunAutoMacro. XlRunAutoMacro can be one of these XlRunAutoMacro constants. xlAutoActivate. Auto_Activate macros xlAutoClose. Auto_Close macros xlAutoDeactivate. Auto_Deactivate macros xlAutoOpen. Auto_Open macros Example This example opens the workbook Analysis.xls and then runs its Auto_Open macro. Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen This example runs the Auto_Close macro for the active workbook and then closes the workbook. With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With -- Regards, Juan Pablo González Excel MVP "CRayF" wrote in message ... I've created an example.VBS file that contains: ----------------- Dim XLApp Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" ----------------- In the myworkbook.xls is an auto run macro ------------- Sub Auto_Open() 'some code End Sub ------------- If I start the myworkbook.xls by itself it runs the Auto_Open() macro *just fine*. However, when my myworkbook.xls is called via the example.VBS script it DOES NOT auto run the macro. Any clues? |
The myworkbook.xls Auto_Open() macro won't run if started by a VBS
Thanks a million Juan. It's a wrap...
example.VBS contents: ------------- Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "myworkbook.xls" xlapp.ActiveWorkbook.RunAutoMacros 1 ------------- |
| All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com