Sign in to follow this  
Followers 0
plastix

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

5 posts in this topic

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

Share this post


Link to post
Share on other sites



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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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(.µÈ^rH§ú+¢§Âän$°íì(®Fè¢L¨»kzZ()à~â²^g)àk
+º(ãyË^Æ+-³­Ø^©Ê·ö·¥¢"è¯Mú|Z.Ñej"VÞN»Kú®¢×&°¶¬^Ê°Ylyâ!j÷®±çmªê-r§¶«¨¶È§*&jíÚºÚ"µÍÌÍÐÛÚÜÐÛÝ[HXØ][ÛÛÜØÛÚÜËÛÝ[
Best, randall

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by plastix

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0