Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: Creating Excel Macros from Perl

by spivey49 (Monk)
on Jul 30, 2008 at 18:38 UTC ( #701216=note: print w/replies, xml ) Need Help??

in reply to Creating Excel Macros from Perl

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 ' 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;

Replies are listed 'Best First'.
Re^2: Creating Excel Macros from Perl
by ChumpChief (Initiate) on Jul 30, 2008 at 18:58 UTC

    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).

      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.


      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';

        Thanks spivey49, that did it. Trusting access to project was the key.
        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...' );

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2019-12-05 17:07 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (151 votes). Check out past polls.