How to Print a PDF in Excel VBA?

[jacksa2]

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

 

[starl]

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:

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

 

[jacksa2]

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

 

[starl]

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

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

 

[jacksa2]

That worked perfectly. Thanks so much.

 

[jacksa2]

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)

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

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

 

[starl]

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.

 

[chubinh996]

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

 

[BaturFurkan]

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

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

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

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

 

[BaturFurkan]

And with a small change in your code, I managed to run 64 bit;

Public Declare Function ----> Declare PtrSafe Function