Microsoft KB Archive/214797

= How To Automate Excel Using Perl for Win32 =

Article ID: 214797

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Office 2000 Developer Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q214797



SUMMARY
Developers familiar with using Perl can take advantage of the Automation capabilities in Perl for Win32 to integrate with applications such as Microsoft Office. This article gives you some example Perl code that sends data to Microsoft Excel and then creates a Chart and PivotTable.



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:  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

  Start notepad.exe and create a new file named perlxl.pl with the following Perl script: use Win32::OLE;

$xlApp = Win32::OLE->new('Excel.Application'); $xlApp->{Visible} = 1;
 * 1) Start Excel and make it visible

$xlBook = $xlApp->Workbooks->Add;
 * 1) Create a new 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";
 * 1) Our data that we will add to the workbook...

$rng = $xlBook->ActiveSheet->Range("A1:C7"); $rng->{Value} = $mydata;
 * 1) Write all the data at once...

$tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");
 * 1) Create a PivotTable for the data...

$tbl->AddFields("Category", "Item"); $tbl->PivotFields("Price")->{Orientation} = 4; # 4=xlDataField
 * 1) Set pivot fields...

$chart = $xlBook->Charts->Add; $chart->SetSourceData($rng, 2); $chart->{ChartType} = 70; # 3D-pie chart $chart->Location(2, "Sheet4");
 * 1) Create a chart too...

print "Press to continue..."; $x = ;
 * 1) Wait for user input...

$xlBook->{Saved} = 1; $xlApp->Quit; $xlBook = 0; $xlApp = 0;
 * 1) Clean up

print "All done.";  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.

Keywords: kb3rdparty kbautomation kbhowto KB214797

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.