Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunAutoMacros does not work correctly from vbscript!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunAutoMacros does not work correctly from vbscript!
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 "Juha Vehvilainen" schreef in bericht ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunAutoMacros does not work correctly from vbscript!
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. Juha Vehvilainen wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif on cells with formula's won't work correctly | Excel Worksheet Functions | |||
SMALL function seems not to work correctly | Excel Worksheet Functions | |||
Sorting numbers doesn't work correctly | New Users to Excel | |||
How do I get Auto-Fit to work correctly? | Excel Worksheet Functions | |||
custom filter does not work correctly | Excel Discussion (Misc queries) |