in reply to Determining if excel workbook is open

Instead of trying to predict in advance what Excel can or cannot do, the easy way is to tell Excel to open the file (workbook) and see what Excel has to say about that. Excel will not allow 2 instances to have read/write access to a file simultaneously.

Try to do this via the normal user interface and you will see what Excel would tell a human being and the options presented to the user when the open fails. How the fancy options like "notify me when the other guy closes the file" work via the API, I have no idea. But I'm sure you will get a clear "Hey, this r/w open attempt did not work".

As an update, if there is some super special requirement, I would consider using the Handle Command. This is a free utility download from Microsoft. It can show all sorts of stuff about open handles on the system. You do of course have to be an admin to run this thing which could be a problem? Using API's that talk to the "guts" of XP require higher level permissions than what a user would need to just run Excel.

  • Comment on Re: Determining if excel workbook is open

Replies are listed 'Best First'.
Re^2: Determining if excel workbook is open
by davies (Monsignor) on Sep 02, 2010 at 00:02 UTC
    Unfortunately, it depends on what you are doing and how Excel is set up. If Application.DisplayAlerts (this is VBA syntax) is false, Excel will open the file as read only without notifying the user. It may be possible to do this and then ask Excel if the file is read only, which, if the file was opened RW, might do the job - I hadn't previously thought of this, and will look into it (it's one in the morning, so not now!). If Application.DisplayAlerts is true, a dialogue box will appear, but I haven't found a way to intercept it and process the message. MerryMonk's node, Is an Excel spreadsheet open?, drew responses about how to tell if a single open instance of Excel has a file open, but as I said in that thread, what if someone has several instances of Excel open? I do, routinely. Therefore, I don't know of a better way within Excel/Perl than the "try to write" approach MerryMonk mentioned.

    Many thanks, Marshall, for the link to the Handle download. I haven't previously hear of it, and I can see all sorts of applications. But, as you point out, it's not useful for applications for lowly users.

    Regards,

    John Davies
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom