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

Working with Perl and Excel 2007 spreadsheets (on a windows XP Pc), I have used assignment statements such as 
$excel->{ActivePrinter} = "Auto HP Deskjet 6500 Series on HOMEDELLTOWE
+R on Ne02:";

[download]
Frequently the problem has been to find out the ‘name’ of the variable to set – for example ActivePrinter.
Therefore I thought it would be useful if I could find out all the variables associated with $excel.
To this end I tried the following, hoping the $excel was a reference to a hash.
use strict ;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel, $workbook, $sheet, $jex);
$excel = CreateObject OLE "Excel.Application";
$workbook = $excel    -> Workbooks -> Add;
print "before jex loop\n";
foreach $jex (sort {$a cmp $b} %$excel) {
    print "$jex <$excel->{$jex}\n";
}
print  "after jex loop\n";

[download]
This failed and I got a ‘windows’ error message saying that “perl.exe has encountered a problem and needs to close. We are sorry for the inconvenience."
What is the correct way of finding out what is ‘in’ $excel?

Replies are listed 'Best First'.
Re: Finding out Excel 'variables'
by Corion (Patriarch) on Jan 15, 2011 at 16:18 UTC

    Short of reading the documentation for the class you're automating, or looking at the type library, you can't find out what properties an OLE object provides.

    I think that Win32::OLE::Const can read OLE type libraries, but I think it only extracts the constants from it and not the properties and methods of objects.

[reply]
Re: Finding out Excel 'variables'
by Gangabass (Vicar) on Jan 16, 2011 at 06:42 UTC

    Alt+F11 in Excel to start VBA and after that F2 to start Object browser.

[reply]
Re: Finding out Excel 'variables'
by davies (Monsignor) on Jan 17, 2011 at 12:24 UTC

    Your approach suffers from a number of misconceptions. Let's look at some of them.

    $Excel is a hash. As I think you have worked out for yourself, it isn't. While it is possible that it may be at the top level, there are lower levels where there are certainly arrays, and I suspect that there are also linked lists, which we will look at later.

    $Excel has only one level below that. While I'm pretty sure you know better, your code implies this. When you get an unspecified element from $Excel, you don't know what the return type is in advance. It may be hash, array, linked list or some other type.

    $Excel is finite. It isn't. This is where we come to linked lists (apologies to language developers if I'm abusing this term). Many objects within Excel have a .Parent element. While there isn't a .child, I will pretend there is to demonstrate the problem, as a large number of children exists. If you imagine object.child, object.child.parent takes you back to the object. You can add infinitely many .parent.child extensions to this. The most I've used in practice is 2, but Excel is unlimited.

    $Excel is consistent. Even assuming a single version and service pack, it isn't. In the simplest case, opening a file will add an element to several arrays, while adding a printer to Windows will give Excel an undefined list of potential new properties that it can address.

    Unfortunately, I think your quest is doomed. Sorry and all that beastly rot.

    Regards,

    John Davies

[reply]
Re: Finding out Excel 'variables'
by planetscape (Chancellor) on Jan 18, 2011 at 11:49 UTC

    See also Save Excel worksheets as PDF with regard to "Excel constants."

    West Wind Technology's GetConstants utility is another option. (Note that they do shuffle the links around occasionally, so it may be necessary to do some searching.)

    I think what you are really looking for, however, is more along the lines of the "Object (Model) Browser", some variation of which you may already have on your machine called "OLE Browser" or "Type Library Browser", if you installed Win32-OLE-0.1403 or upgraded from that to a later version. You might find it in C:\Perl\html\OLE-Browser or C:\Perl\html\lib\site\Win32\OLE depending on your install.

    You may also wish to have a look at Dr. Steven Roman's Object Model Browser Version 2.

    HTH,

    planetscape
[reply]
[d/l]
[select]

      If MerryMonk is after the built-in constants, my reply is barking up the wrong tree. The code to get them is available from the ActiveState site, but I reproduce it here:

      use strict;
use Win32::OLE;
use Win32::OLE::Const;

my $xl = Win32::OLE::Const->Load("Microsoft Excel");
printf "Excel type library contains %d constants:\n", scalar keys %$xl
+;
foreach my $Key (sort keys %$xl) {
    print "$Key = $xl->{$Key}\n";
}

      [download]

      Regards,

      John

[reply]
[d/l]

Back to Seekers of Perl Wisdom