Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

"use Win32::OLE::Const" not having an effect

by HelenCr (Monk)
on Aug 06, 2012 at 04:39 UTC ( #985596=perlquestion: print w/ replies, xml ) Need Help??
HelenCr has asked for the wisdom of the Perl Monks concerning the following question:

Dear wise PerlMonks

I have been working on a project on a Windows 7 system, using StrawberryPerl v 5.014 and Win32::OLE.

I am trying to use a second PC running Windows XP SP3, to have the same project developed in parallel. On the new system, I have installed Strawberry Perl v 5.016, and used cpanp to install Win32::OLE and Win32::OLE::Const.

I am using the same script sources in the new system.

It turns out that in the new system, somehow

use Win32::OLE::Const 'Microsoft Excel';

is not having an effect. I am getting the error:

Bareword "xlExcel8" not allowed while "strict subs" in use in ModuleName.pm line 159.

This error does not show up in the first, original system I mentioned.

What to do?

TIA, Helen

Comment on "use Win32::OLE::Const" not having an effect
Select or Download Code
Re: "use Win32::OLE::Const" not having an effect
by Khen1950fx (Canon) on Aug 06, 2012 at 05:05 UTC
    Try it like this:
    #!/usr/bin/perl use strict; no strict qw/subs/; use Win32::OLE qw/in with/; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

      Just wanted to point out that taking control of an existing instance of Excel could potentially create some problems. In fact, my personal recommendation is to just have the script open a new instance of Excel or at least be aware of the potential problems.

      For more info on the potential problems, you check out some of the posts from davies and I in the Perl r/w Excel with OLE thread. Actually, I think that davies did a better job of explaining the dangers of grabbing control of an existing Excel instance that I did in that thread.

      Khen1950fx: thanks for you reply. But it seems that your solution is a workaround the problem. If, on the original system, everything is working fine, why should I say?: no strict qw/subs/;

      In my opinion, we should try to find out why  use Win32::OLE::Const 'Microsoft Excel'; is not taking an effect on the XP system. For example, maybe XlExcel8 is defined for Excel 2010 (which is installed on the original system), and has a different name in Excel 2003 (installed on the Windows XP system)? (Just an example)?

        Win32::OLE::Const outputs warnings for the following code:

        >perl -wlE "use Win32::OLE::Const 'Some Madeup Library'; print 'All is + well'" No type library matching "Some Madeup Library" found at -e line 1 Win32::OLE(0.1709): GetOleTypeLibObject() Not a Win32::OLE::TypeLib ob +ject at ...

        ... so you will have to look through the Microsoft documentation on Excel to find out what type library to use when.

        For example, maybe XlExcel8 is defined for Excel 2010 (which is installed on the original system), and has a different name in Excel 2003 (installed on the Windows XP system)?

        Somehow I totally overlooked that the error message in your original post was complaining about "xlExcel8". Combined with the fact that you're using Excel 2010 and Excel 2003, I think I know what's going on. The problem is not with the use Win32::OLE::Const 'Microsoft Excel'; line at all.

        Starting with Excel 2007, Microsoft introduced a new Excel file format and used .xlsx file extension as the default file extension for the new file format. Also, with Excel 2007 and Excel 2010, the default file type when saving a file is the new file type. The user can still choose the older file type if he/she wants to.

        Since you're not providing much code, I have to make some guesses. I'm assuming that you've developed your code on your system with Excel 2010 and wanted to save in the older Excel file format. In that case, you have to specify the file format when saving the file. That's where you're using the xlExcel8 constant. However, that's not a defined constant in the Excel 2003 OLE library. Although there is a free update to Excel 2003 that allows it to use the new file format, I can't say for sure if that will add the xlExcel8 constant to the Excel 2003 OLE library.

        If you're planning to have a script that works with Excel versions 2003, 2007 and 2010, then you'll probably need to check to see what version of Excel is installed in order to know which save methods to call. The way that I've done that before was to look at the code from the OLE browser from the Win32::OLE module to see how it dynamically pulled out information about the available OLE libraries. Then I wrote a subroutine that looked for the "Microsoft Excel X.0 Object Library" and used the X value to determine the Excel version. Excel 2010 is 14, Excel 2007 is 12, and I believe that Excel 2003 is 8.

        Here's how to do a quick test of my assumptions. If my assumptions are correct, you probably have something like the following in your code:

        $book->SaveAs({Filename => $filename, FileFormat => xlExcel8});

        On your Excel 2003 system, try changing that line to the following:

        $book->SaveAs({Filename => $filename});

        If I'm right, that should work on your system running Excel 2003.

Re: "use Win32::OLE::Const" not having an effect
by dasgar (Deacon) on Aug 06, 2012 at 05:06 UTC

    First, are you sure that you listed the correct versions of Strawberry Perl? Based on the available downloads from Strawberry Perl's web site, I think you mean version 5.14.2.1 instead of version 5.014 and version 5.16.0.1 instead of version 5.016.

    Do both systems have Excel installed? If the new system does not have Excel installed, then your code will not be able to load the Microsoft Excel Object Library since it won't exist on that system.

      dasgar: thank you for your good reply. I have just answered you, but strangely, it seems the answer has disappeared and doesn't show up in the thread, so I'm trying again.

      a. I think you are right about the Strawberry Perl versions. My original Windows 7 system has 5.14.2.1 installed; but I think you have to say: use 5.014;

      The new Win XP system I'm trying to make work has 5.16.0.1 installed, but one has to say: use 5.016; Isn't that the case?

      b. The original Windows 7 system has Excel 2010 installed. The Win XP system has Excel 2003 installed. Both Excel installations are working fine.

      Many TIA - Helen

        My original Windows 7 system has 5.14.2.1 installed; but I think you have to say: use 5.014; The new Win XP system I'm trying to make work has 5.16.0.1 installed, but one has to say: use 5.016; Isn't that the case?

        Since I have not specified Perl versions like this in my scripts, I'll have to leave that for others who are more knowledgeable to answer that one. I suspect that you would have the same use statement on both systems.

        The original Windows 7 system has Excel 2010 installed. The Win XP system has Excel 2003 installed. Both Excel installations are working fine.

        For the most part, your code should work fine with both versions of Excel. The most notable difference that you need to be aware of is the saving of the Excel file.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://985596]
Approved by dasgar
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2014-04-19 06:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (478 votes), past polls