Perl and Excel 10

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

I have a ‘remote’ user who is using Excel 10 on a Windows 7 PC.
Below is the Perl code that opens an Excel worksheet and adds text to cell B2.

use strict "vars";
use Win32;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel, $workbook, $sheet);
$excel = CreateObject OLE "Excel.Application";
print "excel <$excel>\n";
$excel -> {Visible} = 1;
$workbook = $excel    -> Workbooks -> Add;
print "workbook <$workbook>\n";
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;
print "sheet <$sheet>\n";
$sheet -> Range("B2") -> {Value} = 1234;
[download]

The ‘output’ to my PC where I am using Excel 7 on windows XP is next.

Microsoft Windows XP Version 5.1.2600
(C) Copyright 1985-2001 Microsoft Corp.
excel <OLE=HASH(0x183f828)>
workbook <OLE=HASH(0x183fa58)>
sheet <OLE=HASH(0x183f9c8)>

On the Excel 10 PC the prints for excel and workbook are given and have a similar form to that I obtain.
However, the $sheet-> Activate fails with the message ‘Can’t call method “Activate” on an undefined value at …..
Can anyone tell me how this line (or perhaps the previous one) should be written for Excel 10?

Comment on Perl and Excel 10 Download Code

Replies are listed 'Best First'.
Re: Perl and Excel 10 by davies (Monsignor) on Jan 31, 2011 at 09:42 UTC
Why do you want to activate the sheet? The simple answer to your question is "It shouldn't - regardless of Excel version". Your "use" statements seem strange to me. I would expect to see `use Win32::OLE;` somewhere, and this may be part of your problem. I'm also surprised by your statement that you are using Excel 7, a version that appeared in 1995. My guess is that the strange way you are setting things up means that a workbook is being created with no sheets (I thought this was impossible) or that no workbook is being created. Anyway, on XP Pro and Excel 10 (I use it all the time - it has functionality missing in later versions), the following code seems to me to do what you want. `use strict; use warnings; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add; my $sht = $wb->Sheets("Sheet1"); $sht -> Range("B2") -> {Value} = 1234;` [download] Regards, John Davies Update: if you really need to select the sheet - it can be necessary, rarely - `$sht->Select;` works for me.	[reply] [d/l] [select]
Re^2: Perl and Excel 10 by merrymonk (Hermit) on Jan 31, 2011 at 15:09 UTC
Thank you for that. I have sent off a new test to see what happens.	[reply]
Re: Perl and Excel 10 by bart (Canon) on Jan 31, 2011 at 11:56 UTC
However, the $sheet-> Activate fails with the message ‘Can’t call method “Activate” on an undefined value at …. That implies the previous line didn't return a result: `$sheet = $workbook -> Worksheets("Sheet1");` [download] Perhaps you misspelled the name of the worksheet? Perhaps Microsoft's default naming scheme for the worksheets is different, perhaps due to localization.	[reply] [d/l]
Re^2: Perl and Excel 10 by davies (Monsignor) on Jan 31, 2011 at 12:35 UTC
That's a possibility I didn't consider, but it could very well be the problem. If so, it can be solved by changing the sheet assignment to:`my $sht = $wb->Sheets(1);` Regards, John Davies	[reply] [d/l]
Re^3: Perl and Excel 10 by Anonymous Monk on Jan 31, 2011 at 20:47 UTC
Perl only worked when I used the suggestion of using `$sht = $wb->Sheets(1);` [download] This was on a PC where a Spanish version of Windows is being used. Therefore many thanks to both contributors. I suspect I would have spent many hours trying to figure this one out by myself!	[reply] [d/l]