How to use win32::OLE with multiple versions of Microsoft Office?

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

I have a large perl script which runs on Windows. I use this invocation:

use Win32::OLE;
use Win32::OLE::Const 'Microsoft.Word';    # wd  constants
[download]

To gain access to mso constants, I need this:

use Win32::OLE::Const 'Microsoft Office';
[download]

by experiment, I have found that this does not return mso constants; instead, I get dbo constants, at least on my Windows 7 (x64) perl environment (5.14.1). What does work is:

use Win32::OLE::Const 'Microsoft Office 11.0 Object Library'; # office
+ 2003
[download]

However, this script runs with office 2003, 2007, or 2010 (substitue 12.0 or 14.0, and it will work). Right now, I insert 3 use statements in increasing order:

use Win32::OLE::Const 'Microsoft Office 11.0 Object Library';  # mso c
+onstants
use Win32::OLE::Const 'Microsoft Office 12.0 Object Library';  # mso c
+onstants
use Win32::OLE::Const 'Microsoft Office 14.0 Object Library';  # mso c
+onstants
[download]

but this is ugly and causes nastygrams to be emitted by perl. Monks, what monk-magic would you suggest?

Comment on How to use win32::OLE with multiple versions of Microsoft Office? Select or Download Code

Replies are listed 'Best First'.
Re: How to use win32::OLE with multiple versions of Microsoft Office? by dasgar (Priest) on Aug 03, 2011 at 20:53 UTC
Change that line to be: ~~`use Win32::OLE::Const "Microsoft Excel .* Object Library";`~~ `use Win32::OLE::Const "Microsoft Office .* Object Library";` That should work regardless of which version of Office is installed. However, I'd like to point out that some functions change between versions of Office. That might create some issues for you if you're not anticipating those changes. (Speaking from personal experience on this.) UPDATE:Corrected the use statement to call the correct object library. Left original with strike through for historical reference.	[reply] [d/l] [select]
Re: How to use win32::OLE with multiple versions of Microsoft Office? by davies (Monsignor) on Aug 03, 2011 at 20:59 UTC
In Re^2: Finding out Excel 'variables', I reproduced ActiveState's code for listing Excel's constants and values. I think it would be relatively simple to adapt this to other libraries. I would therefore start thinking along the following lines: Which constants vary from one version to the next? My first guess would be "None", meaning that each later version is likely to be a superset of the previous one. If any constant does in fact vary, how would I know which to use? If you have or can get (not all that difficult in Excel) the version number, you might try creating your own hash of hashes containing all the constants, so that you would have a key, say `xlVisible`, and a hashref giving you the constant values for each version of the library that you have available. This means a large data structure, so my next thought would be to isolate the constants that are actually used. Again, if they are all the same, just use the latest version. Another option is not to use constants at all, but magic numbers. This isn't ideal, but sometimes it's the least work, especially if the constant isn't in the library. So, when writing Perl to control Excel, I frequently write something like: `$object->Color = 255 #vbRed` It would be simple to take this farther and write ones own constants if the magic number were to be used more than once. Regards, John Davies Update: Dasgar's reply, written while I was writing mine, looks much better at first sight. Update 2: fixed minor typo	[reply] [d/l] [select]
Re^2: How to use win32::OLE with multiple versions of Microsoft Office? by frida (Initiate) on Aug 04, 2011 at 15:32 UTC
I agree with John Davies, but the situation here is that the same perl script should run on several different servers. Each server has a particular Windows OS and a particular Office version; I would like to obtain mso constants without hardcoding of any kind. I can figure out various ways to do this, but they all are "not the right (PerlMonk) way". Thanks.	[reply]
Re: How to use win32::OLE with multiple versions of Microsoft Office? by frida (Initiate) on Aug 04, 2011 at 15:10 UTC
To clarify, the only reason I am "Use"ing the Office typelib is to define the mso constants. I was not sure why Dasgar put Microsoft Excel in that line, so I tried this first: `use Win32::OLE::Const 'Microsoft Office .* Object Library'; # mso con +stants` [download] Perl balked, complaining: `No type library matching "Microsoft Office *.0 Object Library" found a +t \winbat\ text2word.pl line 193 Win32::OLE(0.1709): GetOleTypeLibObject() Not a Win32::OLE::TypeLib ob +ject at C: /Perl64/lib/Win32/OLE/Const.pm line 49. Bareword "msoFalse" not allowed while "strict subs" in use at \winbat\ +text2word. pl line 522.` [download] I tried some other incantations, but I either received the No typelib... warning, or no complaint, but no mso... constants, either. Still looking for that magic insight...	[reply] [d/l] [select]
Re^2: How to use win32::OLE with multiple versions of Microsoft Office? by dasgar (Priest) on Aug 04, 2011 at 15:29 UTC
not sure why Dasgar put Microsoft Excel in that line I cut and pasted the wrong line. :D The problem with your use statement is that you used single quotes instead of double quotes. With single quotes, Perl will not do any interpolation, which would occur with double quotes. If you make that one minor change, your code should work.	[reply]
Re^3: How to use win32::OLE with multiple versions of Microsoft Office? by frida (Initiate) on Aug 04, 2011 at 15:45 UTC
Understood. I had used double quotes in the past, but got tired of escaping the period. I tried your version, and I tried this as well: `use Win32::OLE::Const "Microsoft Office \.* Object Library"; # mso co +nstants` [download] but no joy. No complaint from Win32::OLE, but no msoFalse defined... I forget how I figured out that I needed to put in the exact version number (11.0, 12.0, or 14.0), but that is the only way I have been able to get this to work. I tried to figure out a way to select the right version in a BEGIN-END block, but I lack the perlMonkishness to get that going. I can live with one of two solutions (either modify the script for the specific host version of Office -- yuck); or the way it is now (looks ugly as sin, but seems to work; and if multiple versions of office exist, I'll take my chances.	[reply] [d/l]
Re^4: How to use win32::OLE with multiple versions of Microsoft Office? by dasgar (Priest) on Aug 04, 2011 at 19:33 UTC
Re^5: How to use win32::OLE with multiple versions of Microsoft Office? by frida (Initiate) on Aug 08, 2011 at 17:35 UTC
Some notes below your chosen depth have not been shown here