Link to home
Start Free TrialLog in
Avatar of fitaliano
fitalianoFlag for United States of America

asked on

Saving automatically PDF with their default names

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
Avatar of danishani
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rockiroads
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.
Avatar of fitaliano


I figured out the solution by myself