in reply to Question about Win32::OLE and Excel

I would recommend moving all of it into perl. Having the usability/reliability/stability dependant on too many things may cause problems. I have done exactly what you are talking about using Win32::OLE It's not hard. Since it seems you know the VB/Excel macro syntax, you should have less trouble than most in translating this code into perl.

Here is a small example of the syntax translated to perl

#!/perl -w

use strict;
use Win32::OLE;
my $path = "C:\\TEST_EXCEL";

my $excel = new Win32::OLE('Excel.Application');

my $workbook = $excel->Workbooks->Open($path."\\Book1.xls");

$workbook->Worksheets->Add();

$workbook->Save();

$excel->Quit();

[download]
Check the Object Browser in the VB Macro Editor, to see the methods and properties available to each object.

Grygonos

Replies are listed 'Best First'.
Re: Re: Question about Win32::OLE and Excel
by Streen (Sexton) on Apr 20, 2004 at 11:57 UTC
    I try it, but even the simple code below doesn't work and I dont understand why: 
    use strict;
# Create Chart
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;

#my $Book = $Excel->Workbooks->Add;
my $Workbook = "Auswertung022004.xls";
my $activeWorkbook = $Excel->Workbooks->Open($Workbook);

my $Sheet = $activeWorkbook->Worksheets("INF2");

    [download]
    The Error Message is: 
    Can't call method "Worksheets" on an undefined value at H:\work\Auswer
+tungen\ExcelChartWrite.pl line 14.

    [download]

    I would try to activate a Worksheet, even if I replace "INF2" with 1 (which should be the first sheet) the same Error Message shows up, what did I wrong?

    Even if I put the line out, the Excel File Closes imediatly, Because of that it would be much easier to simply put the macro into the Excel file, because the macro has been debuged and runns propperly

[reply]
[d/l]
[select]

      might try using zero based counting instead of 1. Also, the file will try to be opened inside the same directory that the script is running from. If this is not your intent you should specify a path along with the filename

      Grygonos
[reply]
        Also, the file will try to be opened inside the same directory that the script is running from. If this is not your intent you should specify a path along with the filename
        Good advice specifying the full path, but Excel doesn't care about your current working directory or where the script is located.

        Excel looks for the file in its "Default file location", usually My Documents. Check Tools->Options->General->"Default file location".

[reply]
Re: Re: Question about Win32::OLE and Excel
by Streen (Sexton) on Apr 20, 2004 at 13:53 UTC

    Okay now you have convinced me, to convert it into perl, but I hae meet some problems:

    How can i "translate" the following VB code into perl? I tried several times but i didn't get it. 

    Columns(SelectedHeaderColumn).Select

For Each Entry In Selection

$currentsheet->Columns($SelectedHeaderColumn)->{Select}; #this is okay
+ and does what it is suposed to, select the right column

#here comes the problem: this is one example how I tried it
my $selection = $currentsheet->Selection;
foreach my $Line (in $selection){

    [download]
[reply]
[d/l]

      i'm not sure what your code is trying to accomplish.. but when I want to loop through columns I use the following struct 

      for (qw(A B C D E))
{
  $sheet->Range($_."1:".$_."5")->Font->{Bold} = 1;
}

      [download]
      That code takes 5 letters, and loops over them, populating $_ with the current letter each time. The statement inside the loop, Sets the Bold property for rows 1 through 5 of the current column. Is this helping you at all? Try and repost what you want this code to do.

      Hope I'm helping
      Grygonos
[reply]
[d/l]
[select]
        The code should select a column, like: my $SelectedHeaderColumn = "A:A"; #then select the column: $currentsheet->Columns($SelectedHeaderColumn)->{Select}; #then go through all entries in this column ??? in VB this was: 'define Column: Dim SelectedHeaderColumn As String SelectedHeaderColumn = "A:A" 'Select the Column: Columns(SelectedHeaderColumn).Select 'then go through all Entires in the Column: For Each Entry In Selection Debug.Print Entry.Value Next And now I tried to translate the For loop into perl, but I didn't get it... Thanks for help
[reply]

In Section Seekers of Perl Wisdom