Win32::OLE and creation of pivot table in Excel

[Domenico Discepola]

Hello once again. My goal is to create a pivot table in Excel using data
from an existing worksheet in the current workbook. The worksheet is called
'Test Worksheet' and the data resides in cells a1:c3. As I am new to
Win32::OLE, I don't know how to "translate" the following VB Script
(generated from a macro I recorded in Excel) into a "Win32::OLE" / perl
version of those methods:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'Test
Worksheet'!R1C1:R2C3").CreatePivotTable TableDestination:="",
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

Any help would be appreciated.

TIA

 

[Domenico Discepola]

As I am new to

Win32::OLE, I don't know how to "translate" the following VB Script
(generated from a macro I recorded in Excel) into a "Win32::OLE" / perl
version of those methods:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'Test
Worksheet'!R1C1:R2C3").CreatePivotTable TableDestination:="",
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

OK - I read up on "Named Parameters" and found an old post with a concrete
solution to my problem. I was able to generate the pivot table using the
following code;

$workbook->PivotCaches->Add( {SourceType => 1, SourceData => 'Test
Worksheet!R1C1:R2C3'} )->CreatePivotTable( { TableDestination => "",
TableName => "PivotTable1", DefaultVersion => 1 });

You'll notice that the values of certain named parameters have been replaced
with a number (SourceType and DefaultVersion). Does anyone know where can I
read more on this (replacement of parameter values with numbers)?

TIA

 

[Ben Liddicott]

Hi Domenico,

The ActiveState documentation has some pretty good examples, under "Using OLE with Perl", which is in the table of contents under "ActivePerl FAQ/Windows Specific".

Try also
perldoc Win32::OLE

Cheers,
Ben Liddicott

 

[Karlheinz Weindl]

Does anyone know where can I

read more on this (replacement of parameter values with numbers)?

If you work with Perl under Windows you probably have ActiveState's
distribution installed. This distribution comes with HTML documentation
containing an 'OLE Browser' that can be found under 'ActivePerl
Components / Windows Specific'.
Unfortunately it works only with IE!

 

[Domenico Discepola]

As I am new to
Win32::OLE, I don't know how to "translate" the following VB Script
(generated from a macro I recorded in Excel) into a "Win32::OLE" / perl
version of those methods:

OLE with Perl", which is in the table of contents >>under "ActivePerl
FAQ/Windows Specific".

Thank you for your replies. Unfortunately, I don't think I'm understanding
the documentation on Win32::OLE. I've tried Activestate's, Roth's Book and
Microsoft MSDN. My overall goal is to create an Excel worksheet with a
customized pivot table. My code works except when I try to customize the
pivot table:

my $class = "Excel.Application";
my $file = "c:\\temp\\p2.xls";
my @arr01 = ( ["a", "b", "c"], [1, 2, 3] ); #simulate data
my $r = my $c = 0;

unlink( $file ) if ( -e $file ); #delete file if exists

#Create application class called "$excel"
my $excel = Win32::OLE->GetActiveObject( $class );

if ( ! $excel ) {
$excel = new Win32::OLE( $class, \&QuitApp ) || die "Could not create COM
${class} object\n";
}

$excel->{Visible} = 1;
$excel->{SheetsInNewWorkbook} = 1;

#create a new workbook
my $workbook = $excel->Workbooks->Add();

#create a new worksheet
my $worksheet = $workbook->WorkSheets(1);
$worksheet->{Name} = "gah";

#populate the 1st worksheet with data from the array
foreach ( @arr01 ) {
for ( $c = 0; $c<3; $c++ ) {
$worksheet->Cells($r+1, $c+1)->{Value} = $arr01[$r][$c];
}
$r++;
}

#Create the pivot table - hard coded references for now
$workbook->PivotTableWizard( {
SourceType => 1,
SourceData => 'gah!R1C1:R2C3',
TableDestination => "",
TableName => "PivotTable1",
HasAutoFormat => 1
});

#######################################
# PROBLEM HERE
#I want to assign cell "a1" as the pivot table's row field
# since creating the pivot table automatically created a new worksheet, you
must
# reference the new worksheet
#Sample VBScript code taken from MSDN website:
# Worksheets("sheet3").PivotTables(1).PivotFields("year").Orientation =
xlRowField

$workbook->WorkSheets(2)->PivotTables(1)->PivotFields(1)->( {Orientation =>
1});
#######################################3

$workbook->SaveAs( $file );
$workbook->Close();
exit 0;

The error message I get is:
OLE exception from "Microsoft Excel":
Unable to get the PivotTables property of the Worksheet class
Win32::OLE(0.1603) error 0x800a03ec

I assume that this means that there's some kind of problem with the
PivotTables property. I checked the Excel object model
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/ht
ml/xltocObjectModelApplication.asp) and found that the PivotTables property
does belong to the worksheet class.

Any help would be appreciated.