Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Manipulating open excel sheet

by Hydrozoa (Initiate)
on Jul 15, 2011 at 09:16 UTC ( [id://914535]=perlquestion: print w/replies, xml ) Need Help??

Hydrozoa has asked for the wisdom of the Perl Monks concerning the following question:

How do I connect to an open excel sheet using Win32:OLE? I've tried GetObject("Filename") but excel produces an error message telling me it's already open.

Replies are listed 'Best First'.
Re: Manipulating open excel sheet
by davies (Prior) on Jul 15, 2011 at 16:12 UTC

    It's not clear to me what you are trying to do. It sounds as if you are trying to take control of an existing instance of Excel. This is not recommended, but the following code works for me:

    use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->GetActiveObject('Excel.Application'); my $wb = Win32::OLE->GetObject("z:\\data\\perl\\mogul\\sillysums2.xls" +); my $value = $wb->Sheets("Params")->Range("zReportTo")->{Value}; print "$value\n";

    The other possibility is that you are trying to use a Perl specific instance of Excel to open a file that is already open on this or another computer. To do that, you will have to open it read only. So:

    use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); my $wb = $xl->Workbooks->Open("z:\\data\\perl\\mogul\\sillysums2.xls", +,1); my $value = $wb->Sheets("Params")->Range("zReportTo")->{Value}; print "$value\n"; $xl->Close;

    Note the ",,1" in the Open command. Excel can take a long string of parameters when opening a file. The third is the ReadOnly flag, which defaults to 0 (false). There are more elegant ways of doing this, but they are left as an exercise for the reader (translation: I've done it, but I'm too idle to look up the code). You may want to put $xl->{Visible} = 1; in to be able to see the new instance.

    If you are using the second way, you will not be able to save the file under its existing name, but you will be able to save a copy. This is down to the operating system, not Excel.

    It's safe to ignore the business of the "zReportTo" value. That's something I put in any file that uses macros as part of my error handling routine, so on my machines it's a valid test that all is working.

    The reasons that the first method is not recommended are:

    You don't know what the instance with the open file is doing. If it's running a macro that will take hours, you will wait for hours without any information. Update: And if a macro closes the file, you will be royally stuffed.

    You don't know if the other instance has the file open Read Only anyway. I will do this deliberately if I'm doing something risky.

    You don't know which instance you will get. I routinely have multiple instances of Excel open, and if you pick the one that doesn't have the file you want, I can see no way of getting another instance and trying that. If anyone knows a way, I'd be most grateful if you would share the information.

    Regards,

    John Davies

Re: Manipulating open excel sheet
by ww (Archbishop) on Jul 15, 2011 at 11:14 UTC
    Close it.

    Sorry, but TTBOMK, Excel is responsible; effectively locking access to an .xls file when opened.

    ...and if this is shown to be wrong, I'm gonna' learn something I sought for a long time. :-)

Re: Manipulating open excel sheet
by Anonymous Monk on Mar 14, 2014 at 06:53 UTC
    I was able to work on an file already opened by Excel by disabling the warnings:
    my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts}=0; my $Book = $Excel->Workbooks->Open($excel_file);
    Hope it can help people reaching this page with the same problem.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://914535]
Approved by kcott
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (4)
As of 2024-03-29 10:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found