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. | [reply] |
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] |
| [reply] |
Here's how I checked to see if an Excel workbook was open before writing to it with Spreadsheet::WriteExcel. I'm sure there's a better way to do it, but this might get you started:
# Eval block traps errors in Excel file creation; local
# redirection of $SIG{__WARN__} allows error message
# to be checked to see if the file cannot be created because
# it is open. The redirect is only valid within the enclosing
# block.
{
local $SIG{__WARN__} = sub { $errmsg = $_[0] };
eval {$workbook = Spreadsheet::WriteExcel->new($Excelfilename);};
if ($errmsg) {
if ($errmsg =~ /may be in use/) {
print "$Excelfilename is open.\n";
} else {
print "Excel file could not be created: $!\n";
}
}
}
| [reply]
[d/l] |