http://www.perlmonks.org?node_id=637528

the_hawk_1 has asked for the wisdom of the Perl Monks concerning the following question:

Sorry for the long title, I've tried to be precise!

I'm presently creating reports with perl and outpputing (such a nice verb) it directly into and excel file using win32::ole.

Here a part of the code:

sub StartUp { use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; use Win32::OLE::NLS qw(:LOCALE :DATE); my $vtfalse = Variant(VT_BOOL, 0); my $vttrue = Variant(VT_BOOL, 1); my $vtpagesw = Variant(VT_I4, 1); my $vtpagest = Variant(VT_I4, 999); eval {$ex = Win32::OLE->GetActiveObject('Excel.Application','Quit' +)}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]- +>Quit;}) or die "Oops, cannot start Excel"; } $ex->{visible} = 0; # Hide excel to the operato +r $ex->{DisplayAlerts}=0; # Stops the alerts of excel $ex->{SheetsInNewWorkbook} = 2; # Set the number of sheets +in workbook $tb = $ex->Workbooks->Add; # Create the new workbook $sheet = $tb->WorkSheets(2); # Select the sheet $sheet->{Name}="Détailé"; # give the name of the report + to the sheet $sheet->Activate(); # Set the Height and width $sheet->Rows("1")->{RowHeight}="47"; $sheet->Columns("A")->{ColumnWidth}="13,57"; $sheet->Columns("B")->{ColumnWidth}="9,43"; $sheet->Columns("C")->{ColumnWidth}="19"; # Set Vertical and Horizontal alignment $sheet->Columns("A:X")->{HorizontalAlignment} = xlCenter; $sheet->Rows("1:7")->{HorizontalAlignment} = xlLeft; with ($sheet->Range("D8:X8"), 'HorizontalAlignment' =>xlLeft, 'VerticalAlignment' => xlBottom, 'Orientation' => 60, 'WrapText' => $vtfalse, 'IndentLevel' => 0, 'ShrinkToFit' => $vtfalse, 'MergeCells' => $vtfalse, 'ReadingOrder' => xlContext) ; $sheet->Columns("D:P")->{ColumnWidth} = "3,14"; $sheet->Columns("Q:Y")->{ColumnWidth} = "5,29"; $sheet->Columns("D:X")->{NumberFormat}="0"; # Set the "style" $sheet->Range("A2:A3")->Font->{size} = 12; $sheet->Range("A2:A3")->Font->{bold} = 1; $sheet->Range("8:8")->Font->{bold} = 1; with ($sheet->Range("8:8")->Borders(xlEdgeBottom), 'Weight' =>xlMedium, 'LineStyle' => xlContinuous, 'ColorIndex' => xlAutomatic) ; # Write the cells that never change Ins("A2", "Campagne:"); Ins("A3", "Rapport:"); Ins("A5", "Journée d\'appel:"); Ins("A6", "Date du rapport:"); #Split the screen and freeze pane $ex->ActiveWindow->{SplitRow} = 8; $ex->ActiveWindow->{FreezePanes} = 1; with ($sheet->PageSetup, 'FitToPagesWide'=>$vtpagesw, 'FitToPagesTall'=>$vtpagest, 'Zoom'=>$vtfalse, 'PrintGridlines'=>0, 'LeftHeader'=> "", 'CenterHeader' => "", 'RightHeader' => "", 'LeftFooter' => "&IPerformance des agents - Détail - journée d +e traitement: ".DuAu($ARGV[0],$ARGV[1]), 'CenterFooter' => "", 'Orientation' => xlLandscape, 'RightFooter' => "&IPage &P de &T.", 'PrintHeadings'=>0, 'FirstPageNumber'=> xlAutomatic, 'PaperSize' => xlPaperLetter, 'PrintTitleRows' => "8:8", 'LeftMargin' => $ex->Application->InchesToPoints(0.19685039370 +0787), 'RightMargin' => $ex->Application->InchesToPoints(0.1968503937 +00787), 'TopMargin' => $ex->Application->InchesToPoints(0.196850393700 +787), 'BottomMargin' => $ex->Application->InchesToPoints(0.393700787 +401575), 'HeaderMargin' => $ex->Application->InchesToPoints(0.196850393 +700787), 'FooterMargin' => $ex->Application->InchesToPoints(0.196850393 +700787), 'CenterHorizontally' => $vttrue );
and I'm doing the same for the other worksheet.

After entering all the informations, I would like to create buttons that can interact with the file, using unfortunatelty 2 VB macros.

My problem is, how can I create the buttons and associate them with a new macro, created by my perl script/program?

Thanks a lot!

Replies are listed 'Best First'.
Re: Inserting a Button and a Macro in and excel file
by starX (Chaplain) on Sep 07, 2007 at 02:49 UTC
    Been a while since I've had to do any programming in VBA (for which I am exceedingly grateful), but IIRC, the macros in Excel can be imported from an external file. I don't see why you couldn't have your program output something like this, and then import that into Excel. Or maybe there's even a way to use something like Spreadsheet::WriteExcel to do that part of it for you.

Re: Inserting a Button and a Macro in and excel file
by planetscape (Chancellor) on Sep 07, 2007 at 06:15 UTC

    I may be barking up the wrong tree, but I do know that in Access, there are two undocumented features called Application.LoadFromText and Application.SaveAsText which could be used to save "objects" including VBA macros. I can't seem to find these in Excel (when using the Object Browser and choosing "Show Hidden Members"), yet I had thought they existed - or maybe had similarly-named equivalents. I will keep looking (my info dates from <> 2003, so it's pretty well buried), and update this node if I find more info. In the meantime, though, you may want to check The Spreadsheet Page, which is a pretty comprehensive resource for all things Excel and spreadsheet, or perhaps google for microsoft Excel MVPs, and extend your search from there.


    Update: Added a phrase clarifying what the two undocumented methods were used for.

    Update^2: Chip Pearson's page, Programming To The Visual Basic Editor (VBE), may also be of use to you.

    HTH,

    planetscape
Re: Inserting a Button and a Macro in and excel file
by Gangabass (Vicar) on Sep 07, 2007 at 06:13 UTC