Exporting an MS Excel 2003 workbook to PDF via VBA

pdf excel pdf-generation excel-2003 vba
Margaret picture Margaret · Oct 22, 2008 · Viewed 11k times · Source

I have an Excel 2003 workbook that contains a macro to copy certain of its sheets across to a new workbook, then save and close the new workbook. It does this several dozen times, with slightly different sheet selections each time.

I would like to add an extra step to the macro to export the secondary workbooks' spreadsheets to PDF. The obvious way to do this would be to use a PDF printer and Excel's built in Print function, but most PDF printers give you a "Save As..." dialogue box before they finish. Obviously, typing this in individually for seventy-odd occasions lacks appeal - so I'd like something that allows me to set it ahead of time (probably "Use the filename of the file I'm printing minus its extension") then just select the default options.

Any ideas for a free PDF printer that does this? Or a suitable alternative?

Answer

Fionnuala picture Fionnuala · Oct 22, 2008

Office 2007 allows for this. I have found PDFCreator to be good, the VBA is included in sample files, and have heard that CutePDF is also good. PDFCreator and CutePDF are free.

Related questions

Save multiple sheets to .pdf

I have a reporting spreadsheet that grabs some data from a database and forms three sheets of summary report information. I want to run the spreadsheet automatically from the command line, and have it automatically save all three reporting sheets …

Read More
VBA Print to PDF and Save with Automatic File Name

I have a code that prints a selected area in a worksheet to PDF and allows user to select folder and input file name. There are two things I want to do though: Is there a way that the PDF …

Read More
Save Excel range as pdf

I'd like to 'save as' a specific sheet or specific range to pdf. I tried implementing a range into my code. Here is what I've been working with: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "c:\Book1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, …

Read More