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";

[download]

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;

[download]

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

In reply to Re: Manipulating open excel sheet by davies
in thread Manipulating open excel sheet by Hydrozoa

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.