Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

is this file open?

by thnidu (Acolyte)
on Dec 18, 2011 at 03:50 UTC ( #944153=perlquestion: print w/replies, xml ) Need Help??
thnidu has asked for the wisdom of the Perl Monks concerning the following question:

Honored monks:

I use Perl under WinXP and Mac OS X, but this question is specifically for WinXP (although I may want to port the script to Mac somedayP.

This script,, opens the current directory with the filehandle POSTED and looks for filenames beginning with a certain pattern, (/^(\d{6}[b-z]?) /). It analyzes each such filename with a series of regexps, then writes the results of its analysis for each filename to an output file (handle OUT) as tab-delimited text.

When it's done all such files -- when the while(($file = readdir(POSTED))) {...} loop is done -- it closes OUT and calls system to open it in NotePad.

Then I manually "select all" in NotePad, "copy", and close the file. The script then opens an Excel spreadsheet into which I paste the data for further manipulation. Here's the main code:

open(OUT, '>', 'index.txt') or die($!); &byfile; # subroutine to find, analyze, & write filenames printf STDERR ("%d lines processed\nwith header, " . "%d lines written to index.txt", $lines, $lines + 1); close(OUT); system("notepad index.txt"); system("index.xlsx");

Here's the problem: I often run this script iteratively to make sure that new files are getting treated properly, or when tweaking the program. At such times, I already have the spreadsheet open from the previous iteration. The script works all right, but I keep getting error messages on the terminal window: "The process cannot access the file because it is being used by another process." Not only is this a nuisance, but the script has wasted time (I don't know if any significant amount) in vainly trying to open a file that's already open.

My question, then: How can I test whether the spreadsheet is already "being used by another program" (namely, Excel)? I can't do a filehandle test because I didn't open it in Perl.

Respectfully awaiting enlightenment,
Your humble student Thnidu

Replies are listed 'Best First'.
Re: is this file open?
by GrandFather (Sage) on Dec 18, 2011 at 04:32 UTC

    I'd be inclined to skip the file write and Notepad step altogether and simply use Win32::Clipboard to set the text in the clipboard directly.

    True laziness is hard work
Re: is this file open?
by mbethke (Hermit) on Dec 18, 2011 at 05:25 UTC

    What GrandFather said. Or you could even try and write an Excel file directly using Excel::Writer::XLSX.

    The error message most likely comes from Excel itself when it tries to open the file and finds it locked by the existing Excel process. If pasting from Notepad is all you want to do in Excel at this point, writing the file using the above module will get rid of this problem. Otherwise you could try and add a timestamp to the file name so subsequently launched Excel processes won't try to step on each other's files. There is probably a way to find an already running Excel process (I'd guess Win32::Process::Info) and remote-control it using COM or something to reload the file, but I don't really have a clue how to do that, I'd just search the Win32:: module namespace for something that sounds like it would.

Re: is this file open?
by cdarke (Prior) on Dec 19, 2011 at 09:46 UTC
    How can I test whether the spreadsheet is already "being used by another program",br>
    That's actually quite difficult on Windows (although a breeze on Linux).

    Using the Win32 API, given the right privileges, it is possible to get a copy of an open file handle in another process using DuplicateHandle(). However, the only documented API which is available is GetMappedFileName() - which means we must first map the file to our process address space. That does not work with a file of zero length! So if a process has, for example, just opened a file for write access then it might be zero length, and buffering will extend the period when it appears to be empty. With all this going on, there is the possibility of a race condition.
    The SysInternals toolset includes handle.exe to give this information, albeit in an external process, and its output is sent to STDOUT (so run it in a pipe).
    handle.exe uses the Windows Object Manager, which uses undocumented APIs. It iterates through kernel's object namespace.
    On Windows 7, make sure you "Run as administrator".
Re: is this file open?
by nikosv (Chaplain) on Jan 06, 2012 at 18:43 UTC
    Take a look at the Win32::OLE documentation which can tell you if Excel (OLE automation server) or the object is already open:

    Win32::OLE->GetActiveObject(CLASS, DESTRUCTOR) The GetActiveObject() class method returns an OLE reference to a running instance of the specified OLE automation server. It returns undef if the server is not currently active. It will croak if the class is not even registered. The optional DESTRUCTOR method takes either a method name or a code reference. It is executed when the last reference to this object goes away. It is generally considered impolite to stop applications that you did not start yourself.

    Win32::OLE->GetObject(MONIKER, DESTRUCTOR) The GetObject() class method returns an OLE reference to the specified object. The object is specified by a pathname optionally followed by additional item subcomponent separated by exclamation marks '!'. The optional DESTRUCTOR argument has the same semantics as the DESTRUCTOR in new() or GetActiveObject().

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://944153]
Approved by McDarren
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (2)
As of 2018-12-16 11:07 GMT
Find Nodes?
    Voting Booth?
    How many stories does it take before you've heard them all?

    Results (70 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!