Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Making workbook visible with Excel 2013

by chinman (Monk)
on Feb 26, 2015 at 19:19 UTC ( #1117982=perlquestion: print w/replies, xml ) Need Help??

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 line 91. Can't use an undefined value as a HASH reference at 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! .... }


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.


    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
      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
      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!
        ^^^Oops, that was my post above, forgot to login first!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1117982]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2022-08-15 03:37 GMT
Find Nodes?
    Voting Booth?

    No recent polls found