Saadat_saeed has asked for the wisdom of the Perl Monks concerning the following question:

Now Using Win32::OLE I could input Data into an Excel sheet using the perl docs by ActiveState. However now I also want to format.... Here is my code snippet 
$Range = $Sheet->Range("E1");
with (my $r = $Sheet->Columns($Range),
    ColumnWidth => 1);

[download]
Very simple eh? Any reason why it would not work AND not give any error messages as well.

Replies are listed 'Best First'.
Re: OLE & Excel
by NetWallah (Canon) on Mar 11, 2004 at 06:04 UTC
    Try use strict;

    Visual Basic syntax does not work very well in perl.

    Use Spreadsheet::ParseExcel to make data entry and formatting easier.

[reply]
      Also sprach NetWallah: Visual Basic syntax does not work very well in perl.

      I know that this is off-topic, but I haven't been too impressed with how it works in VB, either.

      --
      tbone1, YAPS (Yet Another Perl Schlub)
      And remember, if he succeeds, so what.
      - Chick McGee

[reply]
      But it should work.... I am just using OLE and sticking to it..... Any better way to re-write it so that the OLE syntax works and works good!
[reply]

        Don't waste our time and do what NetWallah suggested, add one line to your script, use strict;. After that, fix all errors that strict.pm uncovers in your script. Then your script will work better.

        A small hint: While Perl works with OLE, Perl does not know about a with statement.

[reply]
Re: OLE & Excel
by Grygonos (Chaplain) on Mar 11, 2004 at 12:35 UTC 
    with $sheet->Columns($Range)
{
    ColumnWidth =>1; 

}

    [download]
    Is the correct syntax I believe... It's untested.... and as a rule I agree.. don't use with... its just a fancy proprietary way of setting attributes.
    Grygonos
[reply]
[d/l]
      I am a newcommer to Perl & programming in general - but if not use "with" then what do I use?
[reply]

        Set the attributes manuslly like this 

            $sheet->Range("A1:J18")->Font->{Size} = 9;

    #Center the sheet
    $sheet->Range("A1:J18")->{HorizontalAlignment} = $$xlConst{'xlHAli
+gnCenter'};

    #Label Averages and Totals
    $sheet->Range("A17")->{Value} = "Sums";
    $sheet->Range("A18")->{Value} = "Averages";

    #Put the values in
    $sheet->Range("A1")->{Value} = "Performance:";
    $sheet->Range("C1")->{Value} = $client;

    #Format the fonts
    $sheet->Range("A1")->Font->{Bold} = TRUE;
    $sheet->Range("A17:A18")->Font->{Bold} = TRUE;
    $sheet->Range("C1")->Font->{Italic} = TRUE;

        [download]
        That is a snippet from an Win32::OLE report I did. with is just a fancy way of saying set the following parameters for this object...

        To explain this in natural language, you could say 

        take my dog and{
  wash ;
  dry  ;
  feed ;
}

        [download]
        which would be like using a with block as opposed to 
         
wash my dog;
dry my dog;
feed my dog;

        [download]
        which is just setting those things explicitly. the with block is syntacticaly tyrrany I tell you !!!!!

        Grygonos
[reply]
[d/l]
[select]
Re: OLE & Excel
by Anonymous Monk on Mar 11, 2004 at 19:50 UTC
    Regardless of what others have said above, the syntax of your use of "with" looks just fine. It's just an odd VB construct that doesn't translate directly to perl. Here's a snippet from Win32::OLE's pod:

            with(OBJECT, PROPERTYNAME => VALUE, ...)

    What you need is a better understanding of Excel's object model and how to translate the VB model into Perl.

    Columns is a collection of Column objects. It applies to a Range object, a Worksheet, or the Application object. Being a collection, a single column object can be referenced by name or by index - but not by a Range object. You have a range, you want the first column in $Range, like so:

    with ($Range->Columns(1), ColumnWidth => 1)

    Or you want the column "E:E" on $Sheet: $Sheet->Columns('E:E')....

    By the way, I wouldn't even use the "with". Just assign the column width directly:

    $Sheet->Columns('E:E')->ColumnWidth = 15;

    You may find cacharbe's Using Win32::OLE and Excel - Tips and Tricks helpful in converting from VBA to perl.

    Hope this helps

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom