Error 9: Subscript Out of Range Dilemma

[Rob94]

Here is my dilemma. The following code snippet listed below depicts an object variable name "xlobject" that holds an instance of "Excel.Application". At this point in the code, I have one workbook assigned to the "xlobject" (meaning that xlobject.workbooks.count = 1). The first line of code is supposed to add an existing workbook to "xlobject", bringing the workbook count of "xlobject" up to 2. The last line is supposed to set the "xlMasterWorkbook" object to the 2nd workbook. When I step through this code in debug mode, it works fine, but when I let it run normally from the browser, it bombs out on the last line with a "Subscript Out of Range" error. Is it that the code is executing too fast to set the "xlMasterWorkbook " when run in the browser? BTW...when I run this in debug mode, the message box reports a value of "2". When I run this normally in the browser, the message box reports a value of "1". I'm pretty sure this is why I'm getting the "Subscript Out of Range" error, but my question is how can I resolve this? Any help is greatly appreciated.

'open master workbook	
xlobject.workbooks.open CS_sSpreadsheetPath &  sMasterWorkbookFileName
MsgBox "Workbook count:" & xlobject.workbooks.count
set xlMasterWorkbook = xlobject.workbooks(2)

Thanks - Rob94

 

[PHV]

Have you tried this ?
'open master workbook
set xlMasterWorkbook = xlobject.workbooks.open(CS_sSpreadsheetPath & sMasterWorkbookFileName)
MsgBox "Workbook count:" & xlobject.workbooks.count

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Rob94]

phv,

the workbook count is still 1....