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!
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.