Exporting an MS Excel 2003 workbook to PDF via VBA

Question

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?

Solution 1

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.

Solution 2

As a development on the (wise) answer from Remou, and since this was what helped me out on my problem too.

I choose pdfcreator (since I already had it installed) and this is what I ended up using:

Sub PDF_Print() 
    Dim p 
    p = ActivePrinter 
    ActivePrinter = "PDFCreator" 
    ActiveDocument.PrintOut 
    ActivePrinter = p 
End Sub 

Works like a charm