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]

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

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

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]

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

Hi, Streen

I think this is what you mean: can I add a VB Macro into a Workbook using Perl... the answer is yes...

I have not coded it in Perl but you can do it in VBA so it should work.

Sub Test_VBComponent_Collection()
    Dim B As Workbook
    Set B = ThisWorkbook

    'Iterate over the VBProjects available in the VBA (VB editor)
    'There is probable only one! VBProjects(0)
    For Each proj In B.Application.VBE.VBProjects
        MsgBox proj.Name
        MsgBox proj.VBComponents.Count
        For Each comp In proj.vbcomponents
                'Check Properties etc
            comp.Add (vbext_ct_ClassModule)

    Next
    
    Next
End Sub
[download]

The VBComponent Object is documented in the help for Excel VBA (although you have to get there via VBAProject/See Also). It has some properties you'll find useful including CodeModule which is very poorly documented but is probably the thing you want to stick the text in...

Update: http://www.google.com/microsoft?q=CodeModule+Excel+VBA has some good links...