How To Automate Excel Using Perl for Win32 (214797)

MORE INFORMATION

Before executing any Perl scripts, you need to have Perl installed and configured on your machine. Because Perl is freely distributed, anyone can provide an implementation of Perl for Win32. Two commonly used ported versions are provided by ActiveState, formerly ActiveWare, and Mortice Kern System (MKS). This example uses ActiveState's Perl for Win32.

Follow the steps below to create and run the sample:

1. If you don't have the latest ActiveState's ActivePerl package installed on your machine, and want to try this example, download it from:

   http://www.activestate.com/ActivePerl

2. Start notepad.exe and create a new file named perlxl.pl with the following Perl script:

   use Win32::OLE;
   
   # Start Excel and make it visible
   $xlApp = Win32::OLE->new('Excel.Application');
   $xlApp->{Visible} = 1;
   
   # Create a new workbook
   $xlBook = $xlApp->Workbooks->Add;
   
   # Our data that we will add to the workbook...
   $mydata = [["Item",     "Category", "Price"], 
              ["Nails",    "Hardware",  "5.25"],
              ["Shirt",    "Clothing", "23.00"],
              ["Hammer",   "Hardware", "16.25"],
              ["Sandwich", "Food",      "5.00"],
              ["Pants",    "Clothing", "31.00"],
              ["Drinks",   "Food",      "2.25"]];
   
   # Write all the data at once...
   $rng = $xlBook->ActiveSheet->Range("A1:C7");
   $rng->{Value} = $mydata;
   
   # Create a PivotTable for the data...
   $tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");
   
   # Set pivot fields...
   $tbl->AddFields("Category", "Item");
   $tbl->PivotFields("Price")->{Orientation} = 4; # 4=xlDataField
   
   # Create a chart too...
   $chart = $xlBook->Charts->Add;
   $chart->SetSourceData($rng, 2);
   $chart->{ChartType} = 70; # 3D-pie chart
   $chart->Location(2, "Sheet4");
   
   # Wait for user input...
   print "Press <return> to continue...";
   $x = <STDIN>;
   
   # Clean up
   $xlBook->{Saved} = 1;
   $xlApp->Quit;
   $xlBook = 0;
   $xlApp = 0;
   
   print "All done.";
   					

3. Type perlxl.pl from the command-line in the directory where you created the file, or double-click it from Explorer. You should see Microsoft Excel start, and a new workbook get created with a PivotTable and a Chart.

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Joe Crump, Microsoft Corporation.