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?
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" <CRayF@discussions.microsoft.com> wrote in message
news:7C2864A1-1A74-4D4D-9DC0-B4C900BA8D02@microsoft.com...
> 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?
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" <CRayF@discussions.microsoft.com> wrote in message
> news:7C2864A1-1A74-4D4D-9DC0-B4C900BA8D02@microsoft.com...
> > 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?
>
>
>
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" <CRayF@discussions.microsoft.com> wrote in message
news:1D6D7C51-D275-4993-BD9C-B9E72E1B4869@microsoft.com...
>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" <CRayF@discussions.microsoft.com> wrote in message
>> news:7C2864A1-1A74-4D4D-9DC0-B4C900BA8D02@microsoft.com...
>> > 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?
>>
>>
>>
xlAutoOpen is a constant defined in Excel which your vbs code has no
access to.
Use 1 instead as Juan Pablo posted.
Tim
"CRayF" <CRayF@discussions.microsoft.com> wrote in message
news:1D6D7C51-D275-4993-BD9C-B9E72E1B4869@microsoft.com...
>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" <CRayF@discussions.microsoft.com> wrote in message
>> news:7C2864A1-1A74-4D4D-9DC0-B4C900BA8D02@microsoft.com...
>> > 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?
>>
>>
>>
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
-------------
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks