Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Inserting a Button and a Macro in and excel file

by the_hawk_1 (Scribe)
on Sep 06, 2007 at 20:33 UTC ( #637528=perlquestion: print w/ replies, xml ) Need Help??
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!

Comment on Inserting a Button and a Macro in and excel file
Download Code
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 Gangabass (Vicar) on Sep 07, 2007 at 06:13 UTC
Re: Inserting a Button and a Macro in and excel file
by planetscape (Canon) 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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://637528]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2015-07-07 05:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (87 votes), past polls