Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

VBA for saving pdf file

Hi, I have a problem to define the default file name when converting an excel file to pdf file in VBA. I set PDF converter as default printer. May I know the VBA code to change the defailt file name by adobe acrobat program in excel? Thanks a lot!!!
[255 byte] By [LilyChan] at [2008-1-10]
«« Performance of Capture and Live View at the same time
»» Send Mail Task not updating the "ToLine" property dynamically
# 1

I don't know of a simple way of doing this - although there may be one! By default, PDF writer generates the PDF file name based on the name of the Excel workbook. If your file is called BOOK999.XLS, then PDF writer will use that as the default name.

Are you looking for some different way of specifying a PDF file name, like creating your own "print" button?

mnptl at 2007-10-3 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2

Almost all printer settings, including the PDF converter's default filename, are controlled by the printer driver, not Excel. In most cases it is extremely difficult to change printer driver settings from Excel VBA. From what I can tell, the only solution involves using code to change registry settings, which should only ever be done with caution, and on most corporate computers these days you won't have been given enough system permissions to be able to do it at all.

Note I don't know the specific PDF converter that you refer to, so it's possible that it's an exception to this - try looking for help from the web site of the company that makes the PDF converter.

However, if you want to bypass the printer dialog box completely, you can do it using the PrintOut method. I'll give two examples, one which assumes that you are using the default printer, and another that sets the printer.

Example 1 - uses default printer

Code Snippet

' Set the default filename

sFileName = "YourFullPathAndFileName.pdf"

' Use the PrintOut method to generate the file

ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
PrintToFile:=True, PrToFileName:=sFileName

Example 2 - sets the printer (see extra notes at end of this post)

Code Snippet

' Set the default filename

sFileName = "YourFullPathAndFileName.pdf"

' Define the printer to use

sPrinterName = "Put your pdf printer name here exactly as it appears in your printers list"

' Set that printer as Excel's default

Application.ActivePrinter = sPrinterName

' Use the PrintOut method to generate the file

ActiveWindow.SelectedSheets.PrintOut Copies:=1, _

ActivePrinter:=sPrinterName, _
PrintToFile:=True, PrToFileName:=sFileName

If you still want a dialog box that asks for the filename, you can do that by putting the following code after the sFileName= line and before the PrintOut line:

Code Snippet

' Call a SaveAs dialog box

sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, _

fileFilter:="Adobe Acrobat (*.pdf), *.pdf")

' Stop the macro if the dialog box is cancelled or the filename is blank

If sFileName = False Then

Exit Sub

End If

The drawback to using the PrintOut method is that you won't be able to change any of the other settings that you can normally change from the printer driver dialog box (such as page size).

Hope this helps.

Notes on Example 2:

  • If you're trying this method, I've found the best way to get the printer name exactly right is to record a macro that prints to that printer, and then copy and paste the printer name as it was recorded.
  • On a network, the printer names can vary from user to user. There's probably a workaround for that, but I don't know it. If anyone wants to suggest one, I'd welcome it!
  • The "Application.ActivePrinter =" line should be redundant because the PrintOut method should use its own ActivePrinter:= setting. However, I have found that occasionally Excel decides to use the default printer instead (I haven't figured out why), so having that extra line helps stop that.
CringingDragon at 2007-10-3 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified