Data table to pdf … via PERL … with a side-trip to EXCEL :-þ

So …. you have a data table and you want to make a pdf of it from within a PERL script and can’t make sense of the available modules? Well then, let Excel do it for you.

This is pieced in part from stuff found out there, but I had to solve a few problems to make it all work.

———————————

Create an Excel document containing the data of interest and format (landscape/portrait etc.) it the way you want it. Save the file.

Record a macro called ‘save2pdf’ in that workbook. Stop recording without actually doing anything … creates an empty macro.

View macros and step into the macro you just made. Paste this text into the VB editor.

——————————–
Option Explicit

Sub save2pdf()

‘ save2pdf Macro


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
“C:\Users\username\Documents\foldername\filename.pdf”, Quality:=xlQualityMinimum, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
———————————

Save the file as a macro-enabled workbook (ie. as filename.xlsm)

Now for the PERL script:

—————————————
use strict;
use warnings;
use Win32::OLE;

my $excel= Win32::OLE->new(‘Excel.Application’)
or die “Could not create Excel.Application!\n”
;
$excel->Workbooks->open( “C:\\Users\\username\\Documents\\foldername\\filename\.xlsm” );
$excel->run( ‘save2pdf’ );
$excel->Workbooks->save( “C:\\Users\\username\\Documents\\foldername\\filename\.xlsm” );
$excel->quit;
—————————————

Don’t leave out the \\ in the PERL script.  You have to escape the \ in the path.

That’s all it takes.

I use this in a task-scheduled event for a table that I import from MySQL. The table is refreshed every day via a PERL script. When the script here is run, the table in Excel is refreshed upon opening and saved to a pdf file. The computer does all the work!