Re: Creating Excel Macros from Perl

[spivey49]

Marto pointed you in the right direction. Here's a couple of options.

Excel macro to create a table of contents:

Sub CreateTableOfContents()
    ' Copyright 2002 MrExcel.com
    ' Determine if there is already a Table of Contents
    ' Assume it is there, and if it is not, it will raise an error
    ' if the Err system variable is > 0, you know the sheet is not the
+re
    Dim WST As Worksheet
    On Error Resume Next
    Set WST = Worksheets("Table of Contents")
    If Not Err = 0 Then
        ' The Table of contents doesn't exist. Add it
        Set WST = Worksheets.Add(Before:=Worksheets(1))
        WST.Name = "TOC"
    End If
    On Error GoTo 0
    
    ' Set up the table of contents page
    WST.[A2] = "Table of Contents"
    With WST.[A6]
        .CurrentRegion.Clear
        .Value = "Subject"
    End With
    WST.[B6] = "Page(s)"
    WST.Range("A1:B1").ColumnWidth = Array(36, 12)
    TOCRow = 7
    PageCount = 0
    ' Do a print preview on all sheets so Excel calcs page breaks
    ' The user must manually close the PrintPreview window
    Msg = "Excel needs to do a print preview to calculate the number o
+f pages. "
    Msg = Msg & "Please dismiss the print preview by clicking close."
    MsgBox Msg
    ActiveWindow.SelectedSheets.PrintPreview
    ' Loop through each sheet, collecting TOC information
' Loop through each sheet, collecting TOC information
    For Each S In Worksheets
        If S.Visible = -1 Then
        S.Select
' Use any one of the following 3 lines
        ThisName = ActiveSheet.Name
        'ThisName = Range("A1").Value
        'ThisName = ActiveSheet.PageSetup.LeftHeader
        HPages = ActiveSheet.HPageBreaks.Count + 1
        VPages = ActiveSheet.VPageBreaks.Count + 1
        ThisPages = HPages * VPages
        ' Enter info about this sheet on TOC
        Sheets("TOC").Select
        Range("A" & TOCRow).Value = ThisName
        Range("B" & TOCRow).NumberFormat = "@"
        If ThisPages = 1 Then
            Range("B" & TOCRow).Value = PageCount + 1 & " "
        Else
            Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCo
+unt + ThisPages
        End If
        PageCount = PageCount + ThisPages
        TOCRow = TOCRow + 1
        End If
    Next S
End Sub

From there you can run the macro from perl using something like $excel->Run(CreateTableOfContents) or convert the macro above. From Marto's link:

How do I convert a VBA macro to Perl?

If you record a macro in Microsoft Office, this can often be translated directly into Perl. In Visual Basic for Applications (VBA) the syntax is like this:

object.method(argument).property = value

In Perl this becomes

object->method(argument)->{property} = value;

So for example this code from VBA:

ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale

becomes this in Perl:

$Chart->Axes(xlCategory, xlPrimary)->{CategoryType} = xlCategoryScale;

[ChumpChief]

Thanks for your reply spivey49, but again that's not really what I'm looking for. I'm looking to use Perl to actually create that macro, not to run it.

Also, that's a slightly different type of TOC from what I'm looking to do -- see my posted code for an example.

I did find this which is exactly what I want -- except it doesn't work. Might be because it was written for Excel 97, I'm not entirely sure (It throws a bareword error on vbextFileTypeModule, apparently it's not included in the constant libraries they're using).

[spivey49]

Try changing "vbextFileTypeModule" to 1 or find the correct constant, possibly vbext_ct_StdModule.

You'll need to set your macro security settings to low and probably trust access to the project as well.

Update:

This works on Excel 2003 after setting macro security settings to low and trusting access to the project:

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE::Const "Microsoft Visual Basic for Applications Extensi
+bility";


my $xls = Win32::OLE->new('Excel.Application');
$xls->{Visible} = -1;
my $wb = $xls->Workbooks->Add;
my $mod = $wb->VBProject->VBComponents->Add(1);
$mod->{Name}="NewMod";
$mod->CodeModule->AddFromString ( <<MODTEXT);
Sub Message
    dim s as string
    s = "Hello, World"
    MsgBox s
End Sub
MODTEXT

my $sheet = $xls->Sheets('Sheet1');
my $button = $sheet->Buttons->Add(71.25, 18.75, 88.5, 57);
$button->{OnAction} = 'Message';

[ChumpChief]

Thanks spivey49, that did it. Trusting access to project was the key.

[Anonymous Monk]

adding the Sub to Codemodule and calling it from perl worked fine {with below code}. How do I call this Sub again from Excel, when i open vb editor I did not see any module with the Sub created from perl. Please help!

my $code = <<'END_CODE';
Sub dosomething(say As String)
For Each cell In ActiveSheet.Range("A1:A10")
cell.Value = say
Next cell
End Sub

END_CODE

my $codemodule = $mod->Codemodule;
$codemodule->AddFromString($code);

$xlApp->Run(
'dosomething',
'test...'
);