Calling Workbooks.Open

Question

This VBScript errs as "unknown runtime error", on the VBScript line that calls the application object's RUN method, which I'm quite sure I'm using correctly, syntactically.

With only one workbook open under the application object, I probably don't even need to prefix it with the Workbookname.
It appears the previous line (which sets the wb variable to the application object's Workbooks.Open method, does not open. Nor does it err, nor does it "eventually" open. The path is correct, the filename is correct, there is no On Error Resume Next in my VBScript.
How can the workbook not open and not error?

I hope I am wrong in thinking there is a "wait/pause" issue. Acting directly on the Excel object model, it seems like the VBScript code waiting until the excelapp object has opened the workbook should be a given?

dim excelapp, wb
set excelapp = createobject("excel.application")
excelapp.enableevents=true
excelapp.visible=true
set wb = excelapp.workbooks.open("C:\Users\John\Desktop\Scheduled Jobs from Isaac\Availability.xlsb")
'MSGBOX "SHOULD BE OPEN"
excelapp.windowstate = -4137    'value for constant xlMaximized acccording to msdn
excelapp.caption = "Running AVAILABILITY - Please WAIT..."
excelapp.run "Availability.xlsb!ChangeAndCopyFile"

Can you do a If wb is Nothing Then Msgbox "hi" to debug? Also why not use the Microsoft Scripting Runtime library's FileSystemObject to detect if the file exists? Also, you can engineer a pause/wait using VBScript's InputBox wsh2.uw.hu/ch08b.html — S Meaden
As to your last question -- highlight the block of code and then press Ctrl + k — John Coleman
Have you tried a file path for the Excel file without spaces? — hstay
Dir(FilePath) will return the file name if the file exist. Len(Dir(FilePath)) will return a number > 0 it the file exists. Since a number <> 0 will evaluate as True then If Len(Dir( "C:\Users\John\Desktop\Scheduled Jobs from Isaac\Availability.xlsb")) then will execute if the file exists. — user6432984
The problem is probably in ChangeAndCopyFile. . Can you post that code? — user6432984

Isaac Isaac · Accepted Answer · 2016-08-06T18:48:17

final vbscript code i used, for better or worse:

dim excelapp, wb
'set excelapp = createobject("excel.application")
'excelapp.enableevents=true
'excelapp.visible=true
'set wb = excelapp.workbooks.open("C:\Users\John\Desktop\ScheduledJobsfromIsaac\Availability.xlsb")
'although I don't know why the above line is silently failing, i'm going to use an alternate method I read on S.O.:
set wb = GetObject("C:\Users\John\Desktop\ScheduledJobsfromIsaac\Availability.xlsb")
set excelapp = getobject(,"excel.application")
excelapp.enableevents=true
excelapp.visible=true
excelapp.windowstate = -4137    'value for constant xlMaximized acccording to msdn
excelapp.caption = "Running AVAILABILITY - Please WAIT..."
excelapp.run "Availability.xlsb!ChangeAndCopyFile"
wb.close(true)
excelapp.caption = "10 seconds to close - please WAIT..."
wscript.sleep 10000
excelapp.displayalerts=false
excelapp.quit

Calling Workbooks.Open

2 Answers