Saving automatically PDF with their default names

fitaliano used Ask the Experts™
I have the following routine in Excel
Sub PrintMultipleFiles()

   Dim filenames As Variant
   Dim counter As Integer

   ' set the array to a variable and the True is for multi-select
   filenames = Application.GetOpenFilename(, , , , True)

      counter = 1

      ' Ubound determines how many items in the array
      While counter <= UBound(filenames)

         'Opens the selected files
         Workbooks.Open filenames(counter)
         Sheets(Array("DB p1", "DB p2")).Select
         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
         Sheets("DB p1").Select
         'Close the selected files
         ActiveWorkbook.Close (True)
         'Increment counter
         counter = counter + 1

End Sub

It allows me to select files, automatically open them and print them on pdf if I set my pdf as default printer BUT the only drwback is that I get a popup asking me to save the PDF with the Excel file name.PDF and then it shows the pdf file...

Is there anyway to eliminate the PDF popup asking to save and the subsequent pdf file display?

I basically want the file to be autoamtically saved with whatever name is already assigned to the Excel file.PDF

Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You might find you answer in simular thread in here:

Top Expert 2006

What s/w are you using to print? All pdf print drivers I have come across will ask for a filename. Only one I have seen which has vba code to avoid asking is pdf995. I have used it to dump reports but never excel. However excel sample code can be found here

What version of office are you using? Later versions allow you to save as pdf. I know you can with A2010, with A2007 you need the SaveAsPDF addon. I have used it in Access but I assume the same would work with Excel.


I figured out the solution by myself

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial