Making workbook visible with Excel 2013

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

Greetings! I have a script that mucks around in Excel, which I've been using since Office 2000. Suddenly and tragically, I am having issues with Excel 2013 users. I need to make the workbook visible, otherwise when I save the Excel file, the workbook will be hidden when the user tries to open it. I have used the code below successfully for years to accomplish this. I have marked the offending line below in the code. When this is used with Excel 2013 on Win7 x64, I get the following error:

Win32::OLE(0.1712) error 0x8002000b: "Invalid index" in METHOD/PROPERTYGET "Windows" at blah.pl line 91. Can't use an undefined value as a HASH reference at blah.pl line 91.

Has anyone else run into this, or have any suggestions?

use strict;
use Win32::OLE qw(with);
use Win32::OLE::Const 'Microsoft Excel';

sub open_sheet {
    my $file = shift;
    my $workbook = Win32::OLE->GetObject( "$file" )
                                || die "Could not locate $file.\n";
    my $excelApp = $workbook->{Application};
    $excelApp->{Visible} = 1;
    # ****this next line no longer works in Excel 2013****
    $excelApp->Windows( $workbook->{Name} )->{Visible} = 1;
    # ^^^^bad!
    ....
}
[download]

Thanks!

Comment on Making workbook visible with Excel 2013 Download Code

Replies are listed 'Best First'.
Re: Making workbook visible with Excel 2013 by CountZero (Bishop) on Feb 26, 2015 at 19:42 UTC
Just a guess, but either `$excelapp` is not properly initialized or `Windows` is no longer a method of the Excel object. You will have to check the documentation for the object model of Excel 2013 to make sure. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply] [d/l] [select]
Re^2: Making workbook visible with Excel 2013 by Anonymous Monk on Feb 26, 2015 at 20:38 UTC
The object model seems to be the same on old versus new Excel. If you record a VBA macro to unhide a workbook with this version of Excel you basically get: `Windows("workbook_name.xls").Visible = True` [download] Which is what you get on earlier versions. Oddly, `Visible` does not appear to be a property of `Windows` in either version of Excel, so maybe it should have never worked. The way I was doing it was right out of Dave Roth's book. There's got to be a way with the current version of Excel to accomplish this!	[reply] [d/l] [select]
Re^3: Making workbook visible with Excel 2013 by chinman (Monk) on Feb 26, 2015 at 21:11 UTC
^^^Oops, that was my post above, forgot to login first!	[reply]


more useful options
	PerlMonks