RunAutoMacros does not work correctly from vbscript!

[Juha Vehvilainen]

Hi

I create an Excel workbook through VBScript:
Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = TRUE
set objWkB = objXL.WorkBooks.add( "book.xlt" )

After creating I pass the parameters into the first
worksheet:
Set colArgs = WScript.Arguments
For i = 0 to colArgs.Count - 1
objXL.Cells(i + 1, 1).Value = "Parameter(" & i & ")"
objXL.Cells(i + 1, 2).Value = colArgs(i)
Next

After this I try to run "Open" macro by command:
objXL.WorkBooks.Item(1).RunAutoMacros(1)

Problem is that the autoOpen subroutine is called right
after workbook.add command when there are no parameters
on the worksheet.
When .RunAutoMacros command is executed the control never
passes to autoOpen subroutine!

I have Win XP with latest updates and Office 2000 with
SP3.

Does anyone have a solution to this.

Juha

 

[magician]

Try it without the Items-keyword, like this:

Dim objXL
Dim objWkB

Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = TRUE
Set objWkB = objXL.WorkBooks.Add("book.xlt")

Set colArgs = WScript.Arguments
For i = 0 to colArgs.Count - 1
objXL.Cells(i + 1, 1).Value = "Parameter(" & i & ")"
objXL.Cells(i + 1, 2).Value = colArgs(i)
Next

objXL.WorkBooks(1).RunAutoMacros(1)



Hans

 

[Dave Peterson]

I created a .vbs with your code (very slightly modified):

Dim objXL
Dim objWkB
Dim i

Set objXL = wscript.CreateObject("Excel.Application")
objXL.Visible = True
Set objWkB = objXL.WorkBooks.Add("c:\my documents\excel\book1.xlt")

'After creating I pass the parameters into the first worksheet:
Set colArgs = WScript.Arguments
For i = 0 To colArgs.Count - 1
objXL.Cells(i + 1, 1).Value = "Parameter(" & i & ")"
objXL.Cells(i + 1, 2).Value = colArgs(i)
Next

'After this I try to run "Open" macro by command:
objXL.WorkBooks.Item(1).RunAutoMacros 1


An in my book1.xlt file, I had this in my Auto_open() code (in a general
module):

Option Explicit
Sub auto_Open()
MsgBox Worksheets(1).Range("a1").Value
End Sub

And I could see A1 and A2 both change when I ran the .vbs via
windows start button|run.

C:\WINDOWS\Desktop\aaa.vbs "xyz"

and it always showed "Parameter(0)"

So I'm gonna guess that it's not your .vbs file that's screwing things up. I'm
gonna guess that the code that's running that causing the problem is not
Auto_Open, it's workbook_open.

When you open a workbook, Auto_open only runs if you run it.

But workbook_open fires UNLESS you tell it not to.

So you could try moving all the open stuff to auto_open (and not change your VBS
file). Or you could tell your VBS file not to run workbook_open.

objxl.enableevents = false
Set objWkB = objXL.WorkBooks.Add("c:\my documents\excel\book1.xlt")
objxl.enableevents = true

Would stop it.

I put this in my workbook_open (under ThisWorkbook):

Option Explicit
Private Sub Workbook_Open()
MsgBox "wo " & Worksheets(1).Range("a1").Value
End Sub

and this to call it in the .vbs:
objxl.run objwkb.name & "!" & "thisworkbook.workbook_open"

And it worked!

======
But I really think I'd try to put the code in the excel template under
Auto_open. It just seems easier to me.