How to print from excel to pdf without typing in a filename

Question

Is there a way to get around having to type in a filename when sending a document to Adobe PDF printers? My code is:

Application.ActivePrinter = "Adobe PDF on Ne01:"
MyWorkbook.PrintOut From:=1, To:=3, Copies:=2, Collate:=True

This works except that it prompts me for a filename. Would rather find a way to fix this without installing any extra libraries so it would be portable.

Thanks

Answer 1

Does this help at all?

"How To: Changing the Adobe PDF Printer/Distiller Printer Settings"

"Issue

You wish to automate printing from a third party application to either Adobe PDF or Distiller print drivers so that no UI elements are invoked for example 'Do not send fonts to Distiller', 'View results in Acrobat', 'Prompt for PDF Filename', 'Delete log files for successful jobs', and the 'Ask to Replace existing PDF file' preferences when you print.

Solution..."

Answer 2

I had to solve a similar problem where I had to create PDFs with automatically-generated file names.

In my solution, I installed PDF Creator, which acts like a printer on your computer, except that it saves PDF files when you print to it. One of its settings allows you to automatically save a PDF to a directory when you print to PDF Creator.

I set up my Excel application to print repeatedly to PDF Creator. I can't remember which it was (perhaps the name of the worksheet?), but one of the sheet's properties controlled the title of the document and thus the name of the saved file.

This isn't an ideal situation because it requires you to manually toggle a setting in PDF Creator, but it worked very well for a simple application I ran on my own machine.