How To Print A PDF In Excel VBA? | MrExcel Message Board

• Forums New posts Search forums Board Rules
• What's new Featured content New posts New Excel articles Latest activity
• New posts
• Excel Articles Latest reviews Search Excel articles
• MrExcel Publishing MrExcel Homepage MrExcel Bookstore MrExcel Seminars Excel Consulting Services

Log in Register What's new Search

Search

Everywhere Threads This forum This thread Search titles only Note By: Search Advanced search…

• New posts
• Search forums
• Board Rules

Menu Log in Register Install the app Install How to install the app on iOS

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

• Forums
• Question Forums
• Excel Questions

You are using an out of date browser. It may not display this or other websites correctly.You should upgrade or use an alternative browser. How to Print a PDF in Excel VBA?

• Thread starter Thread starter jacksa2
• Start date Start date Dec 10, 2015
• Tags Tags pdf print

J

jacksa2

New Member

Joined Dec 10, 2015 Messages 4 Hello All, I've been working on being able to print a PDF file from Excel VBA but having a rough time with it. Every example I can find is very different than the next and none have worked. Concept is fairly straight forward. -User inputs file name. -Look for PDF with that name in specified folder -Print it -Close PDF viewer if it opens I have already made code that works for word and excel file types, but cant figure out PDF. I am working with Excel 2010 on windows 7, with Adobe reader 11.0 in the standard C:\Program Files (x86)\Adobe\Reader 11.0 location. Any help would be greatly appreciated! Thanks

Excel Facts

Add Bullets to Range Click here to reveal answer Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

• 1
• 2

Next 1 of 2

Go to page

Go Next Last Sort by date Sort by votes

starl

Administrator

Joined Aug 16, 2002 Messages 6,091 Office Version

1. 365

Platform

1. Windows

Have you tried recording a macro? Since the PDF creator is part of Excel, your Reader version has nothing to do with it. Record a macro of doing a File, Save As, choose PDF as your extension and you'll get what you need. It's not a SaveAs command. You'll get something like this: Code: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\Tracy\Desktop\test.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Upvote 0 J

jacksa2

New Member

Joined Dec 10, 2015 Messages 4 Thanks for the reply. I am not trying to save as a PDF or print as a PDF. I already will have a PDF File in a specific folder. I have them enter the name of the file and folder location in a field and the macro I want looks for the PDF file with that location and file name and prints it. Is this possible? Thanks Upvote 0

starl

Administrator

Joined Aug 16, 2002 Messages 6,091 Office Version

1. 365

Platform

1. Windows

I just reread your original post.. I am sorry - I must have been asleep! I kept reading "print TO pdf" - which would be saving as pdf (used to be a Print option in Excel and still is for some programs). Anyway... This is what I have and it works for me: Windows 7, Excel 2010, Adobe Reader Note that it uses an API call, so won't work on a Mac. Also, the call is 32-bit, so won't work in 64-bit Excel Code: Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Public Function PrintPDF(xlHwnd As Long, FileName As String) As Boolean Dim X As Long On Error Resume Next X = ShellExecute(xlHwnd, "Print", FileName, 0&, 0&, 3) If Err.Number > 0 Then MsgBox Err.Number & ": " & Err.Description PrintPDF = False Else PrintPDF = True End If On Error GoTo 0 End Function Sub PrintSpecificPDF() 'opens the specified pdf and prints it using the default printer 'note that it uses the default PDF program and leaves it open Dim strPth As String, strFile As String strPth = "K:\Personal Documents\Manuals\" strFile = "elliptical assembly.pdf" If Not PrintPDF(0, strPth & strFile) Then MsgBox "Printing failed" End If End Sub Upvote 0 J

jacksa2

New Member

Joined Dec 10, 2015 Messages 4 That worked perfectly. Thanks so much. Upvote 0 J

jacksa2

New Member

Joined Dec 10, 2015 Messages 4 Almost got my program to work the way I want, but am having issues calling a function from my userform. When the Excel file is opened I have a userform automatically pulled up. Two text boxes are filled in, one for folder path and one for file name. Command button is pushed and the file/path is pulled up and printed. Some files will be word, excel, pdf. That part I have finished and thanks to starl' I can now print PDF. But since I have to put the PDF printing function in a Module and I have the UI in a userform, I am having a hard time making them jive together. I want the same concept as in my userform to work with the function. When the user enters a file name and folder that has the PDF I want it to call up the function and Tada, Print. Any help would be great. Thanks Here is my userform code (its a bit long and messy, but it works) Code: Private Sub CommandButton1_Click() Dim WorkOrderName As String Dim PartNumberName As String Dim WorkOrderPath As String Dim bIsValidWorkOrderName As Boolean Dim i As Integer Dim objWord Dim objDoc '__________________________________________________________" 'First Printable 'Get Work Order WorkOrderName = TextBox1.text & ".docx" 'Get Part Number PartNumberName = TextBox2.text & "\" WorkOrderPath = "\\Test\" & PartNumberName Dim strFile As String strFile = Trim(TextBox1.Value) If Len(strFile) = 0 Then MsgBox "You Must Enter a Part Number and Work Order" Else Dim DirFile As String DirFile = "\\test\" & PartNumberName If Len(Dir(DirFile)) = 0 Then MsgBox "File does not exist or may not exist in all Benchmark Tests" Else Set objWord = CreateObject("Word.Application") Set objDoc = objWord.Documents.Open(WorkOrderPath & WorkOrderName) objDoc.PrintOut objWord.Quit End If End If '----------------------------------------------------------------------------------' 'second Printable WorkOrderPath = "\\test2\" & PartNumberName WorkOrderName = TextBox1.text & ".xls" strFile = Trim(TextBox1.Value) If Len(strFile) = 0 Then Exit Sub DirFile = "\\test\" & PartNumberName If Len(Dir(DirFile)) = 0 Then Exit Sub Dim strPathToExcel As String, strSpreadsheetName As String Dim strWorksheetName As String Dim ExcelApp As New Excel.Application Dim ExcelBook As New Excel.Workbook Dim ExcelSheet As New Excel.Worksheet strPathToExcel = WorkOrderPath strSpreadsheetName = WorkOrderName strWorksheetName = "Sheet" ExcelApp.Visible = False Set ExcelBook = ExcelApp.Workbooks.Open(strPathToExcel & strSpreadsheetName) Set ExcelSheet = ExcelBook.Worksheets(strWorksheetName) ExcelSheet.PrintOut ExcelApp.Quit Set ExcelApp = Nothing '----------------------------------------------------------------------------------' Application.Quit End Sub Private Sub CommandButton2_Click() Application.Visible = True End Sub Here is the code for my Function that Prints PDF Code: Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Public Function PrintPDF(xlHwnd As Long, FileName As String) As Boolean Dim X As Long On Error Resume Next X = ShellExecute(xlHwnd, "Print", FileName, 0&, 0&, 3) If Err.Number > 0 Then MsgBox Err.Number & ": " & Err.Description PrintPDF = False Else PrintPDF = True End If On Error GoTo 0 End Function Public Sub PrintSpecificPDF() 'opens the specified pdf and prints it using the default printer 'note that it uses the default PDF program and leaves it open Dim strPth As String, strFile As String If Not PrintPDF(0, WorkOrderPath & WorkOrderName) Then MsgBox "Printing failed" End If End Sub Upvote 0

starl

Administrator

Joined Aug 16, 2002 Messages 6,091 Office Version

1. 365

Platform

1. Windows

You *really* should declare all your variables at the top of the sub. There is no advantage to declaring them scattered throughout your code.. I don't see anything in your userform code trying to print the PDF, so I can't correct what you were trying to do... You don't need to call PrintSpecificPDF. You can just put a call in your userform to PrintPDF and send it the arguments. Take the IF statement out of PrintSpecificPDF and put it in your userform. Upvote 0 C

chubinh996

New Member

Joined Oct 7, 2020 Messages 1 Office Version

1. 2016

Platform

1. Windows

VBA Code: If Err.Number > 0 Then MsgBox Err.Number & ": " & Err.Description PrintPDF = False Else PrintPDF = True End If On Error GoTo 0 End Function Sub PrintSpecificPDF() 'opens the specified pdf and prints it using the default printer 'note that it uses the default PDF program and leaves it open Dim strPth As String, strFile As String strPth = "D:\PDF\" strFile = "B01611.pdf" If Not PrintPDF(0, strPth & strFile) Then MsgBox "Printing failed" End If End Sub I have tried using your code with entering a specific filename. But it reported an error like this. Can you help me see this error? Error is : Invalid Outside Procedure Upvote 0 B

BaturFurkan

New Member

Joined Feb 4, 2021 Messages 4 Office Version

1. 2010

Platform

1. Windows

starl said: I just reread your original post.. I am sorry - I must have been asleep! I kept reading "print TO pdf" - which would be saving as pdf (used to be a Print option in Excel and still is for some programs). Anyway... This is what I have and it works for me: Windows 7, Excel 2010, Adobe Reader Note that it uses an API call, so won't work on a Mac. Also, the call is 32-bit, so won't work in 64-bit Excel Code: Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Public Function PrintPDF(xlHwnd As Long, FileName As String) As Boolean Dim X As Long On Error Resume Next X = ShellExecute(xlHwnd, "Print", FileName, 0&, 0&, 3) If Err.Number > 0 Then MsgBox Err.Number & ": " & Err.Description PrintPDF = False Else PrintPDF = True End If On Error GoTo 0 End Function Sub PrintSpecificPDF() 'opens the specified pdf and prints it using the default printer 'note that it uses the default PDF program and leaves it open Dim strPth As String, strFile As String strPth = "K:\Personal Documents\Manuals\" strFile = "elliptical assembly.pdf" If Not PrintPDF(0, strPth & strFile) Then MsgBox "Printing failed" End If End Sub Click to expand...

I wish you a good day. I made reviews on many excel websites of my country and asked for help. But I couldn't find a solution. I started doing universal research and came across this wonderful and simple solution of yours. And thank you. I have two small questions? Can I print a specific page in the opened pdf file? Can the pdf file be closed after the process is over? Thank you for your help in advance. @starl VBA Code: Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Public Function PrintPDF(xlHwnd As Long, FileName As String) As Boolean Dim X As Long On Error Resume Next X = ShellExecute(xlHwnd, "Print", FileName, 0&, 0&, 3) If Err.Number > 0 Then MsgBox Err.Number & ": " & Err.Description PrintPDF = False Else PrintPDF = True End If On Error GoTo 0 End Function Sub PrintSpecificPDF() Dim strPth As String, strFile As String, strPage As String strPth = Range("A1") strFile = Range("B1") strPage = Range("C1") If Not PrintPDF(0, strPth & strFile) Then MsgBox "Printing failed" End If End Sub Upvote 0 B

BaturFurkan

New Member

Joined Feb 4, 2021 Messages 4 Office Version

1. 2010

Platform

1. Windows

And with a small change in your code, I managed to run 64 bit; Public Declare Function ----> Declare PtrSafe Function Upvote 0

• 1
• 2

Next 1 of 2

Go to page

Go Next Last You must log in or register to reply here.

Similar threads

S

• Question Question

Saving an Excel Range as Word and PDF document

• StevieMP
• Aug 16, 2024
• Excel Questions

Replies 5 Views 500 Aug 19, 2024 Trevor G P

• Solved

Export Worksheets into a Single PDF using Criteria on each Worksheet

• PaulEpic
• Aug 5, 2024
• Excel Questions

Replies 7 Views 651 Aug 7, 2024 jolivanes B

• Question Question

Using VBA to retrieve specific pages from PDFs

• Big_Al_fae_the_rigs
• Oct 31, 2024
• Excel Questions

Replies 1 Views 141 Oct 31, 2024 Big_Al_fae_the_rigs B B

• Question Question

Using VBA and Acrobat Add-in to export sheet as PDF

• Basher515
• Sep 12, 2024
• Excel Questions

Replies 2 Views 378 Sep 12, 2024 Basher515 B K

• Question Question

Top of text gets cut off in newer Excel versions when printing / converting to PDF

• kjhung
• Oct 4, 2024
• Excel Questions

Replies 7 Views 217 Oct 5, 2024 kjhung K Share: Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Share Link

Forum statistics

Threads 1,224,186 Messages 6,177,017 Members 452,755 Latest member anaram

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Share Link

• Forums
• Question Forums
• Excel Questions

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock1)Click on the icon in the browser’s toolbar.2)Click on the icon in the browser’s toolbar.2)Click on the "Pause on this site" option. Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus1)Click on the icon in the browser’s toolbar.2)Click on the toggle to disable it for "mrexcel.com". Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin1)Click on the icon in the browser’s toolbar.2)Click on the "Power" button.3)Click on the "Refresh" button. Go back

Disable uBlock

Follow these easy steps to disable uBlock1)Click on the icon in the browser’s toolbar.2)Click on the "Power" button.3)Click on the "Refresh" button. Go back Continue without adsI've disabled my adblock Back Top