How To Print To PDF With Excel VBA? - Super User

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Jobs
   6. Companies
   7. Unanswered
2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Explore Teams Create a free Team
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams How to print to PDF with Excel VBA? Ask Question Asked 9 years, 3 months ago Modified 3 years, 8 months ago Viewed 49k times 0

As the title suggests... I'm looking to expand my automating to include directly printing to PDF. The macro(s) I have written already formats the print area and page setup as I need it. But when I go to record my macros nothing for printing, changing printers or saving to file is captured.

Is there a way to accomplish this? I could even get by if it just required a save location prompt. The file name I would imagine could be auto-generated by grabbing existing info within the spreadsheet.

• microsoft-excel
• microsoft-excel-2010
• pdf
• vba

Share Improve this question Follow edited Apr 5, 2018 at 2:00 dasMetzger asked Mar 23, 2015 at 18:22 dasMetzgerdasMetzger 17933 gold badges44 silver badges1313 bronze badges 0 Add a comment |

1 Answer 1

Sorted by: Reset to default Highest score (default) Date modified (newest first) Date created (oldest first) 2

if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format. Use the following code in your macro to save your file as PDF

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\46506090\Desktop\Book1.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ True

Here you can replace the path C:\Users\46506090\Desktop\Book1.pdf and file name to your liking.

Edit: If you intend to publish the entire workbook as PDF and not just the sheet you are working on just replace ActiveSheet.ExportAsFixedFormat to ActiveWorkbook.ExportAsFixedFormat

Share Improve this answer Follow edited Mar 23, 2015 at 18:36 answered Mar 23, 2015 at 18:30 PrasannaPrasanna 4,09455 gold badges3535 silver badges5151 bronze badges 4

• Will I be able to customize the filename & location based on other content within the worksheet or previously defined by another sub? Also, will this overwrite any file names already present at that location or give me a prompt to rename? Isn't the default usually a (1) after the filename to avoid overwriting existing files? – dasMetzger Commented Mar 23, 2015 at 18:36
• 2 To add to this answer: In my version of this, it saves the PDF file in the same location as the file being printed. fPath = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, ".") - 1) & " - " & ActiveSheet.Name & ".pdf" – Engineer Toast Commented Mar 23, 2015 at 18:36
• 2 @dasMetzger Sure, you can customize it. Prasanna hard-coded it but you can change it to a variable and then set that variable anywhere you like. You could even put this snippet into a function with the file path as an argument so you can call it elsewhere. – Engineer Toast Commented Mar 23, 2015 at 18:38
• If you want to use the same filename as the Excel file is - you can add @EngineerToast code – Prasanna Commented Mar 23, 2015 at 18:38

Add a comment |

You must log in to answer this question.

Highly active question. Earn 10 reputation (not counting the association bonus) in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.

Not the answer you're looking for? Browse other questions tagged

• microsoft-excel
• microsoft-excel-2010
• pdf
• vba

.

• The Overflow Blog
• How to build open source apps in a highly regulated industry
• Community Products Roadmap Update, July 2024
• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...

Related

0 How to control excel cells together with vba script? 0 Excel VBA: How to use a unique username or save location for an export pdf macro 0 Extract text from PDF with Naunce Power PDF using vba 0 Excel VBA - Print range specified in a cell to Pdf and send by outlook 0 Excel - VBA code to save range as pdf and using cell for file name

Hot Network Questions

• Staying in USA longer than 3 months
• Could someone translate & explain the Mesorah?
• Is it possible to complete a Phd on your own?
• Why can't LaTeX (seem to?) Support Arbitrary Text Sizes?
• Equivariant Smith normal form?
• What’s the highest salary the greedy king can arrange for himself?
• What's the point of Dream Chaser?
• Moving with a questionable advisor
• Is this halting time a non-standard integer?
• lme4 Inconsistency
• How to NDSolve a PDE that contains the integral of the solution?
• A very basic autosegmental tree using forest
• Tikz Border Problem
• Book that I read around 1975, where the main character is a retired space pilot hired to steal an object from a lab called Menlo Park
• Times of a hidden order
• Why would a plane be allowed to fly to LAX but not Maui?
• Why does an activated soft fork not invalidate the blockchain?
• Remove duplicates in file (without sorting!) leaving the _last_ of the occurences
• Books using the axiomatic method
• How to make D&D easier for kids?
• Does the decision of North Korea sending engineering troops to the occupied territory in Ukraine leave them open to further UN sanctions?
• Will electrolysis hydrolyze esters?
• Is there any other reason to stockpile minerals aside preparing for war?
• Why should the Vce be half the value of the supply source?

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb