Re^4: Converting Excel to Pdf

I have just tried using the macro recorder. It gave

Application.ActivePrinter = "deskPDF on DDM:"
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""deskPDF on DDM:"",,TRUE
+,,FALSE)"
[download]

I have done this before but always struggle partly because I have no experience of VBA.
I suspect that Application.ActivePrinter becomes something like Applciation->ActivePrinter but I am not sure what should come before this.
I am not all sure how to change the Execute line.
Finally, although I did give a file name before I stopped the macro I could not see this in the recorded code.
I do mind at all do the work it is just the way to go about it is a bit obscure.

Comment on Re^4: Converting Excel to Pdf Download Code

Replies are listed 'Best First'.
Re^5: Converting Excel to Pdf by Corion (Patriarch) on Jun 15, 2009 at 09:24 UTC
It seems that your PDF printer driver asks you for a filename. That's bad, as you can't (easily/conveniently) remote control that input. It's weird that the macro recorder records a call to `ExecuteExcel4Macro` - in my opinion, it should have recorded a call to `ActiveWorksheet.PrintOut`. But maybe you pressed a special "Print to PDF" button or something instead of pressing the generic "Print" button in Excel? Translating from VB to Win32::OLE is fairly easy, but you'll have to learn a bit of VB or be willing to experiment a bit. Start with: `my $excel = Win32::OLE->new('Excel.Application'); my $workbook = $excel->Workbooks->Add($absolute_path_to_excel_filename +); my $first_worksheet = $workbook->Sheets(1); $first_worksheet->PrintOut();` [download] Then, you'll have to look at what parameters you're allowed to pass to `->PrintOut`. These are documented in the MSDN or the Office Object Model Documentation (available when you press `F2` to get the object browser and then see the documentation of the methods of the `Workbook` object. Also see WIN32, OLE, Excel, and Printing which might have more code and information.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^5: Converting Excel to Pdf
by Corion (Patriarch) on Jun 15, 2009 at 09:24 UTC

It seems that your PDF printer driver asks you for a filename. That's bad, as you can't (easily/conveniently) remote control that input.

It's weird that the macro recorder records a call to ExecuteExcel4Macro - in my opinion, it should have recorded a call to ActiveWorksheet.PrintOut. But maybe you pressed a special "Print to PDF" button or something instead of pressing the generic "Print" button in Excel?

Translating from VB to Win32::OLE is fairly easy, but you'll have to learn a bit of VB or be willing to experiment a bit. Start with:

my $excel = Win32::OLE->new('Excel.Application');
my $workbook = $excel->Workbooks->Add($absolute_path_to_excel_filename
+);
my $first_worksheet = $workbook->Sheets(1);
$first_worksheet->PrintOut();
[download]

Then, you'll have to look at what parameters you're allowed to pass to ->PrintOut. These are documented in the MSDN or the Office Object Model Documentation (available when you press F2 to get the object browser and then see the documentation of the methods of the Workbook object. Also see WIN32, OLE, Excel, and Printing which might have more code and information.

[reply]
[d/l]
[select]