Re: Manipulating open excel sheet

[davies]

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