Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Automate Excel Using Perl for Win32


View products that this article applies to.

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.

↑ Back to the top


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

↑ Back to the top


Keywords: KB214797, kbhowto, kbautomation, kb3rdparty

↑ Back to the top

Article Info
Article ID : 214797
Revision : 7
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 467