Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

Excel 2007 Save Active Workbook as PDF Through VBA

In VBA, how do I invoke the save as dialog box with the PDF file type already selected?  I realize I can use the menu command but this is what the client wants.  So basically, with the active workbook open, the save as dialog box would pop up with the file type selected as PDF.
0
error_prone
Asked:
error_prone
  • 4
  • 4
1 Solution
 
David L. HansenProgrammer AnalystCommented:
Try this:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
        "P:\myPathvariable & newfilenameVariable, _ 
        Quality:=xlQualityMinimum, IncludeDocProperties:=False, IgnorePrintAreas _ 
        :=False, OpenAfterPublish:=False 

Open in new window

0
 
error_proneAuthor Commented:
Like I wrote in my original post, I just want the save as dialog box prompt with the file type of PDF already selected.  I don't want to have to hardcode the file name in VBA.  The user will supply that once the dialog box opens up.  
0
 
David L. HansenProgrammer AnalystCommented:
Sorry, this is perhaps a bit closer to what you want:

ActiveWindow.SelectedSheets.PrintOut Copies:=1
'Pop up a Save As Dialog box
    Dim path As String = Application.GetSaveAsFilename("", "All Files, *.pdf", 1, "Save File As")
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       path, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
error_proneAuthor Commented:
I'm getting a Compile Error here:
Dim path As String = Application.GetSaveAsFilename("", "All Files, *.pdf", 1, "Save File As")

Also, I noticed it says ActiveSheet.  I need it to save the entire workbook as PDF.  Will the code do that?
0
 
error_proneAuthor Commented:
Ok, I fixed the syntax error, but can the code not print the document?  Other than that it works fine.
0
 
David L. HansenProgrammer AnalystCommented:
Did you get it print...if not I can help (sorry for being in and out of the office)?
0
 
error_proneAuthor Commented:
Yes, I removed the first line and changed ActiveSheet to ActiveWorkbook.  I only want it to save not print.  Thanks for your help.
0
 
David L. HansenProgrammer AnalystCommented:
sure thing.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now