How to tell if Excel workbook closed, and not using application.getopenfilename etc

[plastix]

Hi there

I am creating an Excel object, and then opening a workbook. The workbook has macros that start on workbook_open. I can tell the workbook is open as i can collect workbook info like number of worksheets etc. However, the workbook has a userform which prompts to open a CSV file... and when that call is made, it seems that the Excel object no longer references the workbook, and I get an object error...

$xl = ObjCreate("Excel.Application")
If Not IsObj($xl) Then
 MsgBox(64,"Can't manipulate Excel","Can't use Excel COM objects. Maybe Excel not installed...")
 Exit
EndIf

$xl.Workbooks.open(@ScriptDir & "\workbook.xls",3,1,5,"password")
$xl.Sheets("To Do").select
$curbk = $xl.ActiveWorkbook ;try to set pointer to actual workbook

While 1
 If $curbk.WorkSheets.Count = 0 Then ExitLoop
 Sleep(250)
WEnd
msgbox(0,"","exit")

When the workbook starts, all is OK. The moment the Application.GetOpenFilename is called in the VBA and the dialog box appears, my check fails ($curbk.worksheets.count).

My question is, how can I tell when the workbook has been closed by the user, so I can execute the 'tidy up' function... many thanks

[Locodarwin]

Unfortunately, the Workbook objects and containers do not appear support the .Count method, which makes getting the list of open workbooks a little less straightforward.

What about testing against the workbook name in the Workbooks collection using a For...In loop?

Something like this:

Local $fFoundFlag = 0
For $oWorkbook In $xl.Workbooks
        If $oWorkbook.Name = "The Book Name You're Looking For" Then $fFoundFlag = 1
Next

I haven't tested this, but it should work and should give you a general idea on how to go about checking for open workbooks. If the workbook you're looking for is open (meaning, a workbook object exists with the name you're looking for), $fFoundFlag will be True.

-S

[randallc]

Unfortunately, the Workbook objects and containers do not appear support the .Count method, which makes getting the list of open workbooks a little less straightforward.

What about testing against the workbook name in the Workbooks collection using a For...In loop?

Something like this:

Local $fFoundFlag = 0
For $oWorkbook In $xl.Workbooks
        If $oWorkbook.Name = "The Book Name You're Looking For" Then $fFoundFlag = 1
Next
oÝ÷ ØZ½é÷öÛ^²×¶¬nëb¶ÈhºWp¢¹Û!¢é]+ÞÊéÞ­©buæ¨0¶(iº.µÈ^rH§ú+¢§Âän$°íì(®Fè¢L¨»kzZ()à~â²^g)àk
+º(ãyË^Æ+-³­Ø^©Ê·ö·¥¢"è¯Mú|Z.Ñej"VÞN»Kú®¢×&°¶¬^Ê°Ylyâ!j÷®±çmªê-r§¶«¨¶È§*&jíÚºÚ"µÍÌÍÐÛÚÜÐÛÝ[HXØ][ÛÛÜØÛÚÜËÛÝ[

Best, randall

[Locodarwin]

Well, color me green. I took it out of my library because it wouldn't work. And yet you're using it just fine. Thanks for the tip, Randallc.

-S

[plastix]

Hi all

Thanks for replies. I'm pretty sure I tried a number of calls to check if the workbook was open, including $xl.application.workbooks.count - and they all fail the moment. I found a reference on the MS knowledgebase (sadly I cant find the link now) regarding object references. Apparently, there is an unresolved issue in the Office object referencing system whereby objects are not registered properly during automation - and the object reference can be lost the first time the obejct 'loses focus'. This only happens the first time it becomes out of focus. Once it gains focus again, it is registered properly and all automation / OLE etc works ok. This issue fits perfectly with the problem I have - I will try the $xl.Application.workbooks.count reference again - but I'm sure I tried that already.

So, because the workbook I am automating puts itself out of focus either by calling application.getopenfilename / msgbox in the VBA, the reference is lost and I get an object error. My solution has been to trap the obejct error, check that the workbook userform title exists, and continue. interestingly, once it survives this first crash, future msgbox's / getopenfilename calls etc do not cause an object error - again fitting with the obscure MS bug.

Interesting. Thanks again for all your help.

PS - just noticed the forum changes - great !

Edited December 10, 2006 by plastix