Using Excel in Perl failure after OS re-install

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

I have had to rebuild my OS10 system and reinstall Office including Excel. I no longer can use Excel with Perl applciations.
The test Perl which shows this is new.

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";
[download]

On a PC where this is working I get the following from the print

excel <OLE=HASH(0x27415ae1b90)>
workbook <OLE=HASH(0x27415d78a70)>
sheet <OLE=HASH(0x27415d78908)>

On the failing PC I simply get

excel <>
Can't call method "Workbooks" on unblessed reference at D:\radan-docs\radan-f\perlwork\programs\excel-test-1.pz line 16.

This is the $workbook line which is not surprising as the $excel line failed.
I guess that there is some problem with the line getting access to Win32::OLE for Excel.
I have re-installed Office but that did not help.
I did wonder if it might be to do with the places looked by the @INC array but

 print "dollar inc @INC\n";
[download]

gave the same list for both PCS.
I have done this before and had no problems.
Can any Monk suggest ways I can cure this?

Comment on Using Excel in Perl failure after OS re-install Select or Download Code

Replies are listed 'Best First'.
Re: Using Excel in Perl failure after OS re-install by poj (Abbot) on Mar 03, 2019 at 12:59 UTC
`use OLE` is a compatibility layer for applications using the old toplevel OLE.pm. # New code should use Win32::OLE. Try `#!perl use strict; use Win32::OLE::Const "Microsoft Excel"; printf "Perl %s\n",$^V; printf "Win32::OLE Version %s\n",$Win32::OLE::VERSION; my $excel = Win32::OLE->new('Excel.Application') or die Win32::OLE->LastError(); printf "Excel %s\n",$excel->{'version'}; print "excel <$excel>\n"; $excel->{Visible} = 1; my $workbook = $excel->Workbooks->Add or die Win32::OLE->LastError(); print "workbook <$workbook>\n"; my $sheet = $workbook->Worksheets("Sheet1"); $sheet->Activate; print "sheet <$sheet>\n";` [download] poj	[reply] [d/l] [select]
Re^2: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 14:29 UTC
Thank you I tried you suggestion but got the following error on the failing PC Perl v5.28.0 Win32::OLE Version 0.1712 Win32::OLE(0.1712) error 0x800401f3: "Invalid class string" at line with my $excel = Win32::OLE->new('Excel.Application') or die Win32::OLE->LastError(); However on the PC where the test works, you suggestion also works. The Perl version and Win32:OLE version were the same on both PCs. Have you any idea why the systems are giving different results?	[reply]
Re^3: Using Excel in Perl failure after OS re-install by poj (Abbot) on Mar 03, 2019 at 14:38 UTC
Do both PCs have the same version of Excel ?	[reply]
Re^4: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 14:50 UTC
Re^5: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 14:56 UTC
Re^3: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 14:36 UTC
...an update If i remove the last section of the $excel line so that it is `my $excel = Win32::OLE->new('Excel.Application') ;` [download] it does not give the Class error but neither does it work.	[reply] [d/l]
Re: Using Excel in Perl failure after OS re-install by soonix (Chancellor) on Mar 03, 2019 at 14:51 UTC
ISTR there is a setting in Excel to enable/disable OLE, and for newer versions, the default has changed to "disabled".	[reply]
Re^2: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 15:07 UTC
I have had a look at the Excel File --> Options -> Advanced as suggested by a Google search. I could see anything about OLE and the settings in this section were the same for both PCs.	[reply]
Re^3: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 16:47 UTC
Thanks to your suggestions I started looking more deeply into the Excel versions on my PC. The OS came with versions of Office which can be used for a trial and purchased. I deleted these (shown as Office in the apps) and then re-installed my copy of Office Professional. Now all works as it should! Thanks again.	[reply]
Re: Using Excel in Perl failure after OS re-install by merrymonk (Hermit) on Mar 03, 2019 at 12:44 UTC
...an update I have: looked in the registry and found the same file name for ole32.dll on both the working and failing PC found that the size and modification date/time is the same for this dll on both PCs.	[reply]