Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Using Win32::OLE and Excel - Tips and Tricks

by cacharbe (Curate)
on Mar 22, 2002 at 04:56 UTC ( #153486=perltutorial: print w/ replies, xml ) Need Help??

My goal with this tutorial is to answer the same repetitive questions dealing with Win32::OLE and Excel that I see so frequently on Perl Monks, as well as the various Perl lists that I frequent. As always, it's a snap-shot of work in progress, and suggestions for improvement or functions you would like to see are always welcome.

I'm going to cover many topics, ranging from dealing with dates and implementing borders, to sizing columns, turning off those pesky warning dialogs, iterating through a collection of worksheets and shading and formatting cells and their contents.

Before I go any further, though, there are some tools and texts that I have found indispensable in my work with Perl and OLE, and I think that I should mention them right from the top.

Books

Both of these books attack the art of using perl in the Win32 environment much more succinctly than I ever could (thank you, Dave Roth), and for a much more in-depth discussion on the "how's", "why's" and "where's", I point you to them and Dave's website, www.roth.net.

Resources

You need a good Object Explorer to map the proper methods, objects and properties of each interface. Since I happen to have VB and VC++ on my machine as well, I use the Object Browser that comes standard with VB, and associate the appropriate reference to a project (I actually created an empty project with references to all of the office objects for an easy reference). But for those without those tools, never fear, you still have a chance.

If you are using the ActiveState flavor of Perl, I recommend the "Win32::OLE - Type Library Browser" written by Jan Dubois (who also wrote a nice little tutorial here). Mine was conveniently located in:

<PERLROOT>\html\site\lib\Win32\OLE\Browswer\Browser.html

What follows is an "Out of the Box" piece of code that should run (well, it does on my machine) with one small change (the path information to reflect your current environment near the CHANGE ME comment). To get the most from the code, feel free to play with the lines that I have commented out, including inserting a graphic into a worksheet.

At the end I have some snippets that didn't really fit in with the complete code, but they are useful in the everyday use of Perl and Excel.

Update - I'm working on a rewrite that removes the comments from the code and puts them in plain text surrounding the lines being discussed, as per a CB conversation with jeffa and dws
UpdateII - Done
UpdateIII - http://support.microsoft.com/default.aspx?scid=kb;[LN];257757 is a nice resource regarding using OLE in a web environement and some hazards you might face (I'm not sure how to make that a link, given the internal use of square brackets, guidance welcome)

Update - Added more snippets regarding inserting sheets in a specific order.

The Code

#!c:\perl\bin\ use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; use Win32::OLE::NLS qw(:LOCALE :DATE); $Win32::OLE::Warn = 3; # Die on Errors. # ::Warn = 2; throws the errors, but # # expects that the programmer deals # my $excelfile = '<MYPATH>\perltut.xls';
First, we need an excel object to work with, so if there isn't an open one, we create a new one, and we define how the object is going to exit
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
For the sake of this program, we'll turn off all those pesky alert boxes, such as the SaveAs response "This file already exists", etc. using the DisplayAlerts property.
$Excel->{DisplayAlerts}=0;
Now, we'll add a new WorkBook to the collection of our very own and immediately save it with our predeclared file name
my $Book = $Excel->Workbooks->Add(); $Book->SaveAs($excelfile); #Good habit when working with OLE, save +often.
Of Course, we could just as easily opened an existing file to work with like so:
my $Book = $Excel->Workbooks->Open($excelfile);
Now we create a reference to a worksheet object and activate the sheet to give it focus so that actions taken on the workbook or application objects occur on this sheet unless otherwise specified.
my $Sheet = $Book->Worksheets("Sheet1"); $Sheet->Activate(); $Sheet->{Name} = "DidItInPerl";
As a first example of inserting data into a WorkSheet and as a preamble to using variants later on I thought we'd use a date value and a piece of text longer than the unformatted cell width.
my ($mday,$mon,$year) = (localtime(time))[3,4,5]; $year += 1900; my $str = $mon.'/'.$mday.'/'.$year; $Sheet->Range("a1")->{Value} = $str; $Sheet->Range("c1")->{Value} = "This is a long piece of text";
This is a study in a few things. The first is iterating over a range and the second is exploring the colors available to us for the interior background color. There are 56 colors in the indexed color palette. If you want a larger palette, you can use the ->{Color} property instead, and pass it an integer value as generated by the VBA function RGB (RGB(Red,Green,Blue)), an exercise I leave to the reader.

Following the iteration, I look at changing the style of a cells text by changing the font and font alignment, as well as changing the widths of the columns and adding gridlines using the WITH method.

foreach my $y(1..56){ my $range = 'b'.$y; $Sheet->Range($range)->Interior->{ColorIndex} =$y; $Sheet->Range($range)->{Value} = $y; } my $range = "A1"; $Sheet->Range($range)->Interior->{ColorIndex} =27; $Sheet->Range($range)->Font->{FontStyle}="Bold"; $Sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter; my @columnheaders = qw(A:B); foreach my $range(@columnheaders){ $Sheet->Columns($range)->AutoFit(); } $Sheet->Columns("c")->{ColumnWidth}=56; my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop xlI +nsideHorizontal xlInsideVertical); $range = "b1:c56"; foreach my $edge (@edges){ with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)), LineStyle =>xlContinuous, Weight => xlThin , ColorIndex => 1); } #$Excel->ActiveSheet->Pictures->Insert(<PATH TO THE PIC>);
And now for a brief example using a Variant data type and it's methods. Update: Added other variant as per example in this node
my $dt = Variant(VT_DATE, $Sheet->Range("a1")->{Value}); print "$dt\n"; print $dt->Date(DATE_LONGDATE), "\n"; $Sheet->Range("a2")->{Value} = $dt->Date(DATE_LONGDATE); print $dt->Date("ddd',' MMM dd yy"), "\n"; $Sheet->Range("a3")->{Value}= $dt->Date("ddd',' MMM dd yy"); #Some Properties Take Variant ints or longs - #in this case the Zoom property MUST be set for the #FitToPages(Wide|Tall) to be acknowledged. my $vtfalse = Variant(VT_BOOL, 0); my $vtpages = Variant(VT_I4, 1); with ($Sheet->PageSetup, 'FitToPagesWide'=>$vtpages, 'FitToPagesTall'=>$vtpages, 'Zoom'=>$vtfalse, 'PrintGridlines'=>0, 'LeftHeader'=> "Using Perl and Excel", 'CenterHeader' => "My First Script", 'RightHeader' => "Test", 'LeftFooter' => "Done", 'CenterFooter' => $dt->Date("ddd',' MMM dd yy"), 'Orientation' => xlLandscape, 'RightFooter' => "", 'PrintHeadings'=>0, 'FirstPageNumber'=> xlAutomatic, 'PrintTitleRows' => "1:1"); #$Book->Save(); #$Book = $Excel->Workbooks->Close();
Now for the

Snippets

In an earlier thread I suggested that the monk iterate through the worsheets object using the ordinal postion of each sheet in the collection, however you can also do this by using the 'in' method of the OLE object. I will show both here for examples sake.

Range Example

my $sheetcnt = $Book->Worksheets->Count(); foreach (1..$sheetcnt){ print "\t" .$Book->Worksheets($_)->{Name} ."\n"; }

'IN' Example

foreach my $Sheet(in $Book->{Worksheets}){ print "\t" .$Sheet->{Name} ."\n"; }

Finding the last Column and Row

Knowing where your data ends is useful. If you want to find the outer edges of the data in your spreadsheet, the following is what I recommend.

my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $LastCol = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column};

Adding a Chart Object

You have all this data, what if you want to create some dynamic Charts?

# Create the chart by dimension # my $Chart = $Sheet->ChartObjects->Add(200, 200, 200, 200);
We're going to use the beauty of the ChartWizard to initialize the data object within the chart object and just give a dummy range. Then, we'll add the bar graphs in after the data object is created.
$Chart->Chart->ChartWizard({Source =>$Sheet->Cells(1)}); $Chart->Chart->SeriesCollection(1)->{Values}= [19,3,24,56,34, 33 +, 16, 10, 3, 100];

Running a Macro

Although I would rewrite all of the VBA Marcos in Perl (*grin*) if you want to leave old sleeping code dogs lie.
$Excel->Run($MacroName);
Fitting to one landscaped page

So, you say your boss wants to see it all on one page.

with ($Sheet->PageSetup, Zoom => Variant(VT_BOOL, 0), FitToPagesTall => 1, FitToPagesWide => 1, Orientation => xlLandscape);
*new*

Using Before and After with 'Add'

Here are some examples of using before and after to get the sheet exactly where you want it.

In the last spot:
my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book->Wo +rksheets->{Count})}) or die Win32::OLE->LastError();
In the first spot:
my $Sheet = $Book->Worksheets->Add({Before=>$Book->Worksheets(1)}) or +die Win32::OLE->LastError();
After (or before) a specific sheet - Note, you can do this by index or by sheet name:
my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets("Sheet1") +}) or die Win32::OLE->LastError();
I've covered most of the 'Basic' skills I found necessary to get my projects done around the office, but I will attempt to add things as I think of them, or am asked about how they work. Suggestions welcome.

C-.

Comment on Using Win32::OLE and Excel - Tips and Tricks
Select or Download Code
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on May 09, 2002 at 20:35 UTC
    I couldn't get your snippet above to get the outer edges of the rows and columns to work. I get the error
    #[Thu May 9 16:39:02 2002] fndsxmit.pl: Win32::OLE(0.1501) error 0x80 +020005: "Ty #pe mismatch" #[Thu May 9 16:39:02 2002] fndsxmit.pl: in METHOD/PROPERTYGET "Fi +nd" argumen #t "SearchDirection" at fndsxmit.pl line 1350
    What am I doing wrong?
      hmmm. It makes me think that you didn't include the line:
      use Win32::OLE::Const 'Microsoft Excel';

      C-.
        ah, exactly right. many thanks. I'm continuously amazed and impressed by the Perl community. Dooj
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jun 07, 2002 at 22:20 UTC
    thanks-great stuff mate -dan
Re: Using Win32::OLE and Excel - Tips and Tricks
by tgo80 (Initiate) on Jun 21, 2002 at 20:00 UTC
    Hey there,

    I just discovered the monks! I havent been a Perl programmer for a long time... its kinda new.

    Thank God for your "Finding the last Column and Row"! I couldn't have done anything without it! I tried to do it myself, or look on the Web, I couldn't find anything 'till I found this one!

    But... unless this is asking to much... HOW DOES IT WORK? I dont get it... Why is it searching for a "*"?

    Thanks!

    Keep doing a great job!

    To
      I'm searching for everything (or better, anything), starting at the end (either last row, or last column).

      The row/column properties return the first area in the specified range that matches, so in this case, it starts at the end of the sheet, either last row or last column,(because of the SearchDirection=>xlPrevious) looking for anything, finds it in the last row/column with data in it and returns that (as an int).

      Hope that makes sense.

      C-.

Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jun 25, 2002 at 17:06 UTC
    Very good and useful article!!! By the way, I'm using it to update values on Excel, but I'd like to know if it's possible to update a cell while the excel file is open. I tried the simple way :
    my $oExcel = Win32::OLE->new('Excel.Application','Quit');
    my $oBook = $oExcel->Workbooks->Open("$excelFile");
    my $oSheet = $oBook->Worksheets(1);
    ...
    $oBook->Save;
    $oBook->Close;

    But it doesn't seem to work...
    Any idea????
    Thanx
      I'm not sure that I understand your question. ALL of the above code assumes that the worksheet is open (Either by creating a new one, or by opening an existing one).

      Are you getting some kind of error, or are you not seeing what you expect? The code that you seem to have omitted would probably help me determine what the matter is, and don't forget to use <CODE> </CODE> tags, it makes for much easier reading.

      C-.

      ---
      Flex the Geek

        In fact, my problem is more about Excel rather than perl (I think).
        I was wondering if there is an option so when my perl program update a value in a cell I can see it immediately in Excel (the file is always open on my desktop)
      The excel must be opened by the script. if the file is already opened the script will fail. In other words your script cannot modified an opened file if it was not opened by your script
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Sep 30, 2002 at 12:51 UTC
    Hi I've got a little problem with reading unicode characters from an Excel Sheet, I thought use utf8; would do the trick, but my cells contain only question marks instead of the unicode characters from the perl point of view. Text => ??? Value => ??? Value2 => ??? This is perl, v5.6.1 built for MSWin32-x86-multi-thread, from ActiveState. I tried it with Spreadsheet::ParseExcel; before, same result. Feels like I'm forgetting something important :-\
      Do you have an example of a your Unicode string so that I can test? Odds are you are going to be playing with Variant (specifically VT_BSTR), but I don't want to steer you in the wrong direction.

      C-.

      ---
      Flex the Geek

      Actually, I was faced with the same problem.

      After fixing other issues (thank god for PerlMonks!), I found the cure:

      use Win32::OLE qw(CP_UTF8); ... # Work in unicode! $Win32::OLE::CP = CP_UTF8; ...
      You can use Unicode::String to unpack() the string to look at each unicode char (which was what I had to do).

      Cheers
      ---Lars

        Actually, using Unicode::String as a container for your data is not needed (in fact, it will croak on acctented chars and other punctuation). Just use the string as you 'normally' do, i.e. to look at each char:

        for my $uchar (split(//, $text)) { my $ord = ord($uchar); ... }
        While it seems natural to me now, it took me some time to locate that my troubles with unicode strings was *using* Unicode::String... :-)

        ---Lars

Re: Using Win32::OLE and Excel - Tips and Tricks
by Reverend Phil (Pilgrim) on Oct 30, 2002 at 19:39 UTC
    ++ wonderful tutorial. I've been using much of this functionality for some time, but I can slick a few snips from you to clean up a few lines here and there. And thanks for pointing out the DisplayAlerts property. Man, there's a great deal of hubbub in that object. =)

    -=rev-=
      I needed to open a spreadsheet with links to external files, which or may not be open. The following snippet did the trick:
      my $Book = $Excel->Workbooks->Open("$EXCELFILE", TRUE);
Last cell?
by Nkuvu (Priest) on Dec 06, 2002 at 21:58 UTC
    What's the advantage to the method you propose for finding the last column and row as opposed to something more like
    # Assuming all variables declared in a "my" statement above. # Also assuming $worksheet is pointing to a WorkSheet object. $last_cell = $worksheet->Range("A1")->EntireColumn-> SpecialCells(xlCellTypeLastCell)->{Address}; ($last_col, $last_row) = $last_cell =~ /\$(\w+)\$(\d+)/;
    ?

    (Note that the last cell returned by this method does not depend on the Range("A1") specified -- it still returns the last cell properly. For example, N23 (as text $N$23) if N23 were the last cell in the sheet.)
      TMTOWTDI

      Actually, yours is probably faster.

      C-.

      ---
      Flex the Geek

        What is the best way to determine all of the valid fields and subfields for Perl/OLE/Excel information. It is only by studying examples did I learn that Cell->Interior->ColorIndex gets the cell background color, and everything I have learned is by Web examples. Is there a definitive document for all of the possible fields?

      Upon further investigation, I have found that this is not reliable if the columnar data is not of equal row size. It returns the last row of the right most column, which is incorrect in some cases.

      The methods I used in the examples return the last row (over all) and the last column (over all) vs finding the location of the last cell in the last column

      So, if the data on the worksheet is symmetric, your function works faster, however yours would not be reliable to, say, put edges around all the cells in a data set that has columns of different row sizes

      C-.

      ---
      Flex the Geek

        Interesting. I don't seem to have run into this, but I wasn't explicitly looking for it, either. I'll have to do some more research on the matter.

        If the method you'd come up originally doesn't work fine with non "square" sheets then what would you recommend.
        Furthermore, I can't seem to be able to make your original last row/column script work. The dos prompt tells me the following :
        Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou +rce\readExcel.pm line 30. Bareword "xlByRows" not allowed while "strict subs" in use at D:\sourc +e\readExcel.pm line 30. Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou +rce\readExcel.pm line 33. Bareword "xlByColumns" not allowed while "strict subs" in use at D:\so +urce\readExcel.pm line 33.
        What should I do ?

        edit (broquaint): changed <pre> to <code> tags

        Sorry about the previous post - I found out what wasn't working about the xlPrevious and I included the
        use Win32::OLE::Const 'Microsoft Excel';
        line. Thanks again and great tutorial. How about PowerPoint someday ?
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 14, 2003 at 18:23 UTC
    Hi cacharbe,
    I'm trying to fill up an excel sheet following a format wich is stored in an excel template (just in a regular excel file).
    This tutorial is really help me, but there's still something hard to do : find out wich cells are merged in the template and merging the matching cells in the output file.
    I have found the MergeCells property, a boolean that tells you if there are a merged cell in the parent object. MergeArea returns a range of merged cells containing a given cell. My problem is I cant find a way to extract the row & col indexes to reproduce the merge on the output file.
    Any idea about, it.
    mehdi aziz
    mehdiaziz@lycos.com
      Hi aziz, Sorry that I am asking you a question instead of an answer.Can u give me the syntax to merge cells in excel using perl(with OLEs)? Thanks, Sree
        I know how to read an Excel cell's hyperlink value. What is the syntax to set or change a cell's hyperlink value? -docuSwear
      I have a problem with this:

      my $indents = $Sheet->Range( "A1:B2" )->{'IndentLevel'};

      If i use 'Value' in stead of IndentLevel everything works fine.

      Any ansers?
Re: Using Win32::OLE and Excel - Tips and Tricks
by kersht (Initiate) on Jan 15, 2003 at 19:16 UTC
    Great "tutorial" on Excel with Win32::OLE! I am trying to extract data from one Excel file, and input that data into another Excel file. The problem comes when trying to have two open Excel files, and make the data transfer happen in the same block. I was thinking of creating a subroutine for each function -- one for extracting the data and sticking it in an array, and one for inputing the data from the array into the other spreadsheet ... However, before I go through the effort, I thought I'd ask the Pro's! Any good guidance or experience in this or a similar Excel problems? Thanks ...
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Feb 07, 2003 at 14:24 UTC
    Well done. It helped me lot, especially at the begining. There is one think I am missing here: How to control the perl script from excel? In other words how to send commnads to perl script and change the sript run. Something like "DDEpoke channel,..." in old DDE. The OLE event is supposed to do it, but I was not able to find any code example how to use it. Do you have any or do you know where to find it? Thanks Tomas
      is it possible to work with 2 workbooks at a time using perl?
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jul 16, 2003 at 04:25 UTC
    Is there any tutorial here or somewhere to create pie chart or any other chart using win32::OLE and excel? thanks

      What have you tried? Did it work? Did you take my advice up top and research the object model? Based on the Chart / Graph info I provided and a little reading, you should be able to figure this out. Even Google can be your friend. I read the second link (which gave me some ideas about ADSI and perl, btw) and found it interesting and helpful and David Wagner bubbled some of Jan DuBois' code up to top a while back here. Give these a read and give it a try.

      Let us know how it goes.

      C-.

      ---
      Flex the Geek

        Thanks :) nice articles and it's very helpful.
        Hello Monk, Just read your documentation on Excel, and found it extremely useful. I have a problem and looking for some help regarding this. This is my code below
        #!/usr/bin/perl use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $MacroName = "LargeFileImport"; #Running a macro $excelfile = "c:\\SndExMail\\test.xls"; my $Book = $Excel->Workbooks->Open($excelfile); my $Sheet = $Book->Worksheets("Sheet1"); $Sheet->Activate(); $Excel->Run($MacroName);
        I am running a macro, which will generate another excel file with certain values spanning across multiple sheets. Now after i execute the above code, a pop is displayed asking me to save sheet1 (this is generated after the macro is run). I can set displayalerts to 0 but At the same time i want to save the excel file generated by the macro with some name so that i ccan open it for manipulations later. Please let me know how to go about doing this.
      Here's a little example that may help other novice monks:
      my $Chart; unless ($Book->Charts->Count) { $Chart = $Book->Charts->Add({After => $Sheet}); # $Sheet is my data sheet $Chart->{Name} = "Graphics"; $Book->Save(); } else { $Chart = $Book->Charts("Graphics"); } $Chart->SetSourceData($Sheet->Range('A1','C124'),xlColumns); # Range, +xlRowCol $Chart->{ChartType} = xlXYScatterLines; # xlChartType; $Chart->Legend->{Position} = xlLegendPositionBottom; # xlLegendPositio +n $Chart->Activate(); # Axes my $Xaxes = $Chart->Axes(xlCategory, xlPrimary); $Xaxes->{HasTitle} = 1; $Xaxes->{AxisTitle}->{Characters}->{Text} = "seconds"; my $Yaxes = $Chart->Axes(xlValue, xlPrimary); $Yaxes->{HasTitle} = 1; $Yaxes->{AxisTitle}->{Characters}->{Text} = "number";
Re: Using Win32::OLE and Excel - Tips and Tricks
by Discipulus (Curate) on Aug 20, 2003 at 08:08 UTC
    thanks for this tutorial but I needed to your line
    my $Sheet = $Book->Worksheets("Sheet1"); #in this way to make it run my $Sheet = $Book->Worksheets(1);
    or I get this error:

    Win32::OLE(0.1601) error 0x8002000b: "Indice non valido" in METHOD/PROPERTYGET "Worksheets" at C:\prova\OLEexcel.pl line 20

    why ??
    thanks 4theTuT Lor*

      Looks to me like you are using an italian?? version of Excel, where I would guess that "Sheet1" is named "Foglio1" or some such.

      Accessing the sheet through index ie "1" in your example works, because you avoid the language differences

      HTH
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Dec 24, 2003 at 14:56 UTC
    ++article :-D ---> many thanks. Being new to perl this article is the closest thing I have for the bible right now... But... I have a few more question: I'm looking for a way to search for a certain value in my spreadsheet and getting it's location (inside my selected area). Finding is the easy part: $Sheet->Range("A1:B10")->Find("text to find") but now what??? 10x a 10^6, grupy.
      Hi,
      If you look at the VB from a macro it is... Cells(r,c).Find(...).Activate which tells you that the $Sheet->Range("A1:B10")->Find() operation is returning something... that you can "->Activate" it means that it is a Range object you're getting back... so you can try
      my $Range=$Sheet->Range("A1:B10")->Find("bob");

      HTH - Mark

        Wow... Thanks a lot!!! it was a great help, even though it took me a while to figure out how to extract the info I needed from the return value (it returns a hash so I had to do something like casting just to see the possible values...) cheers for the quick (and usefull) reply! grupy.
Re: Using Win32::OLE and Excel - Tips and Tricks
by davies (Vicar) on Apr 23, 2005 at 21:04 UTC
    Your reference to the Jan Dubois article gave me a 404 error. Am I just being stupid? I found this via google. Is it the same thing?

    Thanks for the tutorial,

    Regards,

    John Davies
Re: Using Win32::OLE and Excel - Tips and Tricks
by monarch (Priest) on Jul 26, 2005 at 02:46 UTC
    I wanted to add a note on PowerPoint, as when I googled for "powerpoint Win32::OLE tutorial" this node was the very top link!

    So here is how I've gone about creating a PowerPoint presentation.

    First load the modules:

    use Win32::OLE; use Win32::OLE::Const 'Microsoft Office'; use Win32::OLE::Const 'Microsoft PowerPoint'; use strict;
    ..then setting warnings as per the Excel tutorial..
    $Win32::OLE::Warn = 3; # die on errors

    Next, name the file that will be saved:

    my $filename = "c:\\temp\\testpower.ppt";
    .. and then fire up the PowerPoint application
    print( "Starting Powerpoint Object\n" ); my $power = Win32::OLE->GetActiveObject('Powerpoint.Application') || Win32::OLE->new('Powerpoint.Application', 'Quit');

    Create a presentation (much like creating a workbook in Excel)

    print( "Creating a presentation\n" ); my $ppt = $power->Presentations->Add(); $ppt->SaveAs($filename);
    (and save!).

    Create a slide (like creating a worksheet in Excel)

    print( "Creating a slide\n" ); my $slide = $ppt->Slides->Add(1, ppLayoutBlank); $ppt->SaveAs($filename);

    Insert a picture into my slide

    my $pname = 'C:\WINNT\Web\Wallpaper\Fall Memories.jpg'; my $shape = $slide->Shapes->AddPicture( $pname, msoFalse, msoTrue, 20, 1 ); # scale to 50% of original size $shape->ScaleHeight( 0.5, msoTrue, msoScaleFromTopLeft ); $shape->ScaleWidth( 0.5, msoTrue, msoScaleFromTopLeft ); $ppt->SaveAs($filename);

    Insert a table

    print( "Adding a 4 wide by 3 high table\n" ); my $table = $slide->Shapes->AddTable( 3, 4, 1, 100 ); my $columns = $table->Table->Columns->Count; my $rows = $table->Table->Rows->Count; for ( my $row = 0; $row < $rows; $row++ ) { for ( my $col = 0; $col < $columns; $col++ ) { my $cell = $table->Table->Rows($row+1)->Cells($col+1); my $textframe = $cell->Shape->TextFrame; $textframe->TextRange->{Text} = "$col,$row"; $textframe->TextRange->Font->{Bold} = msoFalse; $textframe->TextRange->Font->{Name} = "Arial"; # set text size AFTER changing text $textframe->TextRange->Font->{Size} = "12"; } } $ppt->SaveAs($filename);

    There's a lot more that can be done, of course, and the best reference is at MSDN's Visual Basic Reference for PowerPoint. All the methods and properties used here are listed there. In addition, when calling a method, you often can leave parameters off the end and defaults will be used for you.

    Another note to add.. if you're not running PowerPoint at the time this script is run, then it will happen in the background. The only way to know for sure if it worked is to open the file you created and visually verify everything was added.

      Bareword "msoFalse" "msoTrue" "msoScaleFromTopLeft" not allowed while "strict subs" in use at PPT_ex_2.pl line 25. How can I use the MSWin32 constants without a reference? Thanks...
        I'm seeing the problem with MSWin32 constants and 'use strict'. Anyone know what the problem is ? Any workaround? Thanks, -- Charles DeRykus
      thank ou for sharing the way to handle poewrpoint through perl....i am a beginner in perl and i am trying to display an image on the slide but i am geting the same error as mentioned in the first comment...could you please elaborate on it ?
Re: Using Win32::OLE and Excel - Tips and Tricks
by sandycat05 (Initiate) on Nov 18, 2005 at 00:22 UTC
    Hello, Your tutorial was wonderful! I found a lot of great info. I'm a new Perl programmer, and have been playing around with your code to try to fit my needs. Could you give me any advice on how to proceed with the following task? Instead of either opening a file or creating a new one, I'd like to do BOTH. I was thinking of creating a loop where I could basically say something like: if $excelfile exists, then open $excelfile, otherwise, create a new workbook named $excelfile However, being new to Perl, I haven't quite been able to find the correct syntax. Can you provide any clues as to how to do this, or is it even possible? Thanks, and great work!
Re: Using Win32::OLE and Excel - Tips and Tricks
by iamaids (Initiate) on Nov 24, 2005 at 17:58 UTC
    Thanks for the article. The following code doesn't work on my machine:
    my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop xlI +nsideHorizontal xlInsideVertical); $range = "b1:c56"; foreach my $edge (@edges){ with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)), LineStyle =>xlContinuous, Weight => xlThin , ColorIndex => 1); }
    I'm using ActivePerl 5.8.7. No error messages were encountered. Thanks in advance.
      Perl not able to Recognise the Border Function So please use Win32::OLE::Const 'Microsoft Excel'; It helps to resolve the issue
Re: Using Win32::OLE and Excel - Tips and Tricks
by pinguxx (Initiate) on Nov 26, 2005 at 06:33 UTC
    is there anyway to do freeze_panes with win32-ole, i see that spreadsheet::writeexcel does, can win32 do it?
      $sheet->Cells( $y, $x )->Select(); $Excel->ActiveWindow->{FreezePanes} = 1;
Re: Using Win32::OLE and Excel - Tips and Tricks
by kevinhat (Initiate) on Aug 11, 2006 at 23:25 UTC
    In reference to the "Adding a Chart Object" section above, is there a way to pass an array to the "$Chart->Chart->SeriesCollection(1)->{Values}=" statement?
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 05, 2007 at 14:53 UTC
    Hi, Great tutorial! I'm looking to use text wrap for a column. Is there any chance someone could provide an example of how to do this with Win32::OLE and Excel? Thanks! NoviceMonk.
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on May 15, 2007 at 15:37 UTC
    Thanks for this tutorial, I found how to finding the last Column and Row. But I don't know how to delete one row, one column, or how to sort a sheet ?
      Hi!

      The best advice I've receive was to record a macro in excel, then translate the VBA into your Perl.

      Hope this will help you...

Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)
by Corion (Pope) on Jul 26, 2007 at 11:20 UTC

    In addition to this very good introduction, here's my approach to automating things in Excel that I know how to do manually in Excel. These steps apply to any (Office) application that has a macro recorder:

    1. Start the Excel Macro Recorder
    2. Perform the desired action(s) manually, for example, delete a row or bolden text or create a pivot chart
    3. Stop the Excel Macro Recorder
    4. Inspect the generated VB code
    5. Check that the generated VB code does what you want
    6. Translate the generated VB code to Perl / Win32::OLE invocations

    That's all there is to it and it sure beats poring over the documentation to find out the needed function names.

    Similar lists exist at Re^3: Problems with Microsft's new Office 'XML' and Re: Convert word(.doc) file to html file, but as this appears to be a good starting page for people working with Win32::OLE, I figured to put it here as well.

      I am trying to do this is my program. Open an Excel File Search for a value in the excel file When i find the value i want to know the location of that value. For example if my excel file has 3 values A1-->apple A2-->Orange A3-->Grapes When i search the file for orange..it should say that orange found and return me the value A2. I am not able to find the location. Please help

        Read the Excel object model documentation. For a cell (or Range) there likely is a function returning the coordinates of the upper left corner. Maybe it's Cell->Address or something containing R1C1 in its name.

Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Sep 12, 2007 at 21:09 UTC
    After 2 weeks of losing hair and sleep, this one article shows enough detail for me to move past 'hanging open' excel files and several other issues. I'm writing a tool that has to scan several hundred XLS files in a swamp of folders and extract specific data. Thank you for writing this article, my program will behave so much better for it.
    I noticed your request for handling square brackets and other strange animals in links. Just use the hex representation for them, as in www.badly%5Bformed_url%5D.as.only.MS.can.do
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 11, 2008 at 23:43 UTC
    Thank you, this tutorial was very helpful.
Re: Using Win32::OLE and Excel - Tips and Tricks
by ArmandoG (Sexton) on Jan 25, 2008 at 00:56 UTC
    this tutorial has being my foundation on the project at work thank you, since that said, I am a newbie in Win32::OLE I got this problem I need to copy a cell and cant do it this is the code I am using:
    $Sheet->Range('A17')->Select(); $Sheet->Selection->Copy('A17'); $Sheet->Range('B15')->Select(); $Sheet->ActiveSheet->Paste();
    but that did not work what is wrong? I take this from the VBA in the macro.
    Oh. yes I am using only WIN32::OLE
      Try if it works:

      $range1=$sheet->range('A17');

      $Sheet->copy($range1);

      $range2=$sheet->range('B15');

      $range2=$sheet->paste();

      This piece of code doesn't work because "Selection" is a method of the parent Excel application, not the sheet.

      So if you had, in the beginning of your code:

      my $XcelApp = Win32::OLE->new( 'Excel.Application' );

      Then the second line in your post should go:

      $XcelApp->Selection->Copy('A17');

      Helen

Re: Using Win32::OLE and Excel - Tips and Tricks
by poolboi (Acolyte) on Jan 28, 2008 at 08:50 UTC
    hm..i got a perl program that needs to be appended to the last row using excel how can i do this using win32::OLE? thanks
    use Win32::OLE; # use existing instance if Excel is already running eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Qui +t;}) or die "Oops, cannot start Excel"; } # get a new workbook $book = $ex->Workbooks->Add; $sheet = $book->Worksheets(1); # write a 2 rows by 3 columns range $y = 2; $x = 1; $sheet->Range("A$x:J$y")->{Value} = [['Date','Total (IN)','Suc +c (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ +(OUT)','MO(IN)','MO(OUT)'], [$date, $total_in, $succ_in +,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]]; $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'] +, [$max_pk_msg,$pk_msg]]; foreach(@parameters) { $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}]; $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}]; } # print "XyzzyPerl" $array = $sheet->Range("A2:I1")->{Value}; for (@$array) { for (@$_) { print defined($_) ? "$_|" : "<undef>|"; } print "\n"; } # save and exit $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\pe +rl\\$save_file_name.xls") ; undef $book; undef $ex;
      I was looking at this tutorial, http://www.ngbdigital.com/perl_ole_excel.html. Is there a way that I can sort a column without knowing the lowest & highest range? Thanks.
        Is there any way to define x-axis, y-axis and Chart titles, if i make chart object like $chart = $sheet->ChartObjects->Add(100, 100, 400, 300);
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jul 13, 2008 at 11:23 UTC
    Many thanks for this. It was very useful, and the trick about finding the last row and column was exactly what I was looking for at the time. Graeme
Re: Using Win32::OLE and Excel - Tips and Tricks
by jagdish.eashwar (Novice) on Dec 07, 2008 at 06:07 UTC
    Is it possible to read an excel file without opening it? I have written a small script for reading several excel files in turn. Each of these excel files have a workbook_open macro which puts out a message. Because of this, running the script is very tedious. I have to keep pressing the enter key or click the ok button to get the msgbox out of the way whenever an excel file is opened by the script.
      It would completely depend on the format of the file, but in terms of OLE, no. This methodolgy uses the Excel application as the vehicle to read the files, and it requires the doc to be opened in the engine.

      C-.

      ---
      Flex the Geek

      cacharbe:

      About as easily as you can read a book without opening it... ;^)

      ...roboticus

Check whether the file is in use by an other user
by fish (Novice) on Jul 30, 2009 at 07:35 UTC
    Hi, at first I just want to thank you for the great tutorial, but I still have a problem.
    I am working with a file which is accessible by many users via network. When I open the excel-file I want to check, whether the file is opened by an other user or not. I just want to work with it if it is not opened.
    A simple die() if it is in use would be enough for me.
    So, is it possible to check whether an other user has opened the file?

    Great Regards,
    - fish
      IIRC Windows locks the file when opened. You may be able to check the file lock state, maybe something like this (I have no windows box to test):
      open my $fh, "/some/file" or die "can't open!"; # try/catch file locking eval { flock $fh, 'LOCK_EX' or die "can't lock!" }; if ( $@) { warn "open file!" && exit; }
        Thanks for your response. I tried your code and it's not bad but not exactly what I expected. Instead of showing an error-message if the file is opened the program waits until the file is getting closed. That is not bad, but kind of useless for my problem.

        I am sure we are pretty near to a solution, so can somebody tell me how it works - please ;)

        Best regards,
        - fish
Using Perl from Excel (Re: Using Win32::OLE and Excel - Tips and Tricks)
by Corion (Pope) on Apr 11, 2011 at 07:36 UTC

    If you are caught in the mirror universe and need to use Perl from within Excel, I know two ways of doing that. The first way is a very easy way, and I've supplied information to Excel that way. You simply write a HTTP server in Perl that serves a page that mostly consists of a <TABLE>. That table can be easily accessed and refreshed from within Excel. No special setup of Excel is needed. You just need to link the data in the sheet to the external HTTP address.

    The second way would be through XLLoop, an Excel Add-In that allows you to write Excel function in other languages, and even serve the requests from a central server. I haven't used this, but it could be a way to avoid Visual Basic as glue.

Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Aug 30, 2011 at 19:24 UTC
    Thanks for the informative post. I'd like to add that, for Excel, if you want to save your output in PDF format, you need to use "57" in a SaveAs call:

    $Book->SaveAs($pdf_file_name,57);

    This will save the first sheet as PDF. Does anyone know how to tell Excel to save the entire workbook as PDF?

    TIA,

    Bill

Win32::OLE Excel: script stops due to high excel load
by Ratazong (Prior) on Jul 02, 2012 at 10:58 UTC

    Dear monks!

    I think the following discovery might be useful for you:

    I openend an Excel-workbook to extract some data, as instructed in the node above:

    ... my $Book = $Excel->Workbooks->Open($excelfile); my $Sheet = $Book->Worksheets("Sheet1"); ...
    This worked with some files, but with one I repeatedly got an error-message that the operation could not be done due to some high load in Excel.

    The resulting investigation showed that this special file contained very complex pivot-tables. As a result, opening it seems not to be finished when I tried to access the worksheet. Strange. Fortunately, this could be solved very easily with the following code:

    ... my $Book = $Excel->Workbooks->Open($excelfile); sleep(1); # give Excel time + to open the file my $Sheet = $Book->Worksheets("Sheet1"); ...

    Hope this node helps you (and saves you some debugging-time) in case you encounter the same situation!

    Rata
Re: Using Win32::OLE and Excel - Tips and Tricks
by pank$ (Initiate) on Apr 16, 2013 at 13:17 UTC

    how to use Win32:OLE on UNIX server.....

    i want to modify excel which contains macros ...

    that are deleted when i use ParseExcel::SaveParser

      "how to use Win32:OLE on UNIX server....."

      You can't Win32:: modules are for the Windows platform.

        thanks for replying @marto

        i found articles stating this but they date back to 2001 ......isn't there any module in perl that i can use for this purpose...

        Any help will be highly appreciated:)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perltutorial [id://153486]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (12)
As of 2014-07-31 22:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (253 votes), past polls