Thanks Martin, but that's not quite what I'm after--

In using the macro as a table of contents, the idea is for an end user to be able to click on links in the Excel sheet and have the active sheet changed appropriately. At this point the Perl script is no longer running.

I cobbled together a small version of what I'm trying to do as an example -- my problem is that I can't link to the chart in the TOC:

use strict;
use warnings;

use Cwd;
use Win32::OLE;

my $xlApp = Win32::OLE->new('Excel.Application');
$xlApp->{DisplayAlerts} = 0;
$xlApp->{ScreenUpdating} = 0;

my $xlBook = $xlApp->Workbooks->Add();
$xlBook->SaveAs(cwd . "/example.xls");
print "Saved to " . cwd . "/example.xls";

my $page1 = $xlBook->Worksheets->Add();
$page1->{Name} = "Page One";
$page1->Move({After => $xlBook->Sheets($xlBook->Sheets->{Count})});

my $page2 = $xlBook->Worksheets->Add();
$page2->{Name} = "Page Two";
$page2->Move({After => $xlBook->Sheets($xlBook->Sheets->{Count})});

my $chart1 = $xlBook->Charts->Add();
$chart1->{Name} = "Chart One";
$chart1->Move({After => $xlBook->Sheets($xlBook->Sheets->{Count})});

my $tocSheet = $xlBook->Worksheets->Add();
$tocSheet->{Name} = "Table of Contents";
$tocSheet->Move({Before => $xlBook->Worksheets(1)});

$tocSheet->Hyperlinks->Add({Anchor => $tocSheet->Cells(1, 1),
                            Address => "",
                            SubAddress => "'Page One'!A1",
                            TextToDisplay => "Page One"});

$tocSheet->Hyperlinks->Add({Anchor => $tocSheet->Cells(2, 1),
                            Address => "",
                            SubAddress => "'Page Two'!A1",
                            TextToDisplay => "Page Two"});

#Need some way to link to Chart One here...

$xlBook->Worksheets("Sheet1")->Delete if($xlBook->Worksheets("Sheet1")
+);
$xlBook->Worksheets("Sheet2")->Delete if($xlBook->Worksheets("Sheet2")
+);
$xlBook->Worksheets("Sheet3")->Delete if($xlBook->Worksheets("Sheet3")
+);

$xlBook->Save();
$xlBook->Close();

$xlApp->Quit();
[download]

Sorry it's kinda messy, I blazed through it. Let me know if I can clarify better.

EDIT: In fact, if there's a way to do something like this in Perl, that would be perfect.

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
[download]

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?

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;
[download]

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.

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';
[download]