Announcement

Collapse
No announcement yet.

Print a PDF file using VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Print a PDF file using VBA



    Hi all

    I'm using Excel 2010 and have Adobe Reader 11.

    I'm trying to make a code in VBA that can print a specific PDF file. It's OK if the file opens first but it not necessary.

    The file name change from time to time but itís written in a specific cell in Excel. Let us say that itís A1 in sheet1. The path is the same every time so that can be written directly in the code.

    I have found this code on WWW but it doesnít work for me.

    Code:
    Option Explicit
    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 PrintThisDoc(Formname As Long, FileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(Formname, "Print", FileName, 0&, 0&, 3)
    End Function
     
    Sub testPrint()
    Dim printThis
    Dim strDir As String
    Dim strFile As String
     
    strDir = "J:\KAL\Machines\Extruder I\KÝredata\Tegninger\"
    strFile = "370_Rev.pdf"
     
    printThis = PrintThisDoc(0, strDir & strFile)
    End Sub
    I do hope that someone can help me.

    Thank you in advance

  • #2
    Re: Print a PDF file using VBA

    For AcroRd32.exe, pass command line parameters using Shell().

    /n - Launch a new instance of Reader even if one is already open
    /s - Don't show the splash screen
    /o - Don't show the open file dialog
    /h - Open as a minimized window
    /p <filename> - Open and go straight to the print dialog
    /t <filename> <printername> <drivername> <portname> - Print the file the specified printer.

    Be sure to surround the full path to the EXE and the pdf full path filename with double quote marks.

    Comment


    • #3
      Re: Print a PDF file using VBA

      Hi Kenneth

      Thank you for the answer. Bur don't understand what you mean about the pass command line parameters. Where shall I put in the letters you write?

      Where in the code is the mistake that do that the code don't work??

      Thank you in advance
      /Mads

      Comment


      • #4
        Re: Print a PDF file using VBA

        have a look at the below link - http://www.ozgrid.com/forum/showthread.php?t=146904

        Comment


        • #5
          Re: Print a PDF file using VBA

          You can hard code the path to your EXE as I said. I added an API method to find the path. The command line parameters don't work as well with the newer Adobe Acrobat Reader DC.

          Code:
          Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" _
             (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
          
          Sub Test_Printpdf()
            Dim fn$
            fn = "C:\Users\Ken\Dropbox\Excel\pdf\p1.pdf"
            PrintPDf fn
          End Sub
          
          Sub PrintPDf(fn$)
            Dim pdfEXE$, q$
            
            pdfEXE = ExePath(fn)
            If pdfEXE = "" Then
              MsgBox "No path found to pdf's associated EXE.", vbCritical, "Macro Ending"
              Exit Sub
            End If
            
            q = """"
            'http://help.adobe.com/livedocs/acrobat_sdk/10/Acrobat10_HTMLHelp/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Acrobat10_SDK_HTMLHelp&file=DevFAQ_UnderstandingSDK.22.31.html
            '/s/o/h/p/t
            Shell q & pdfEXE & q & " /s /o /h /t " & q & fn & q, vbHide
          End Sub
          
          Function ExePath(lpFile As String) As String
             Dim lpDirectory As String, sExePath As String, rc As Long
             lpDirectory = "\"
             sExePath = Space(255)
             rc = FindExecutable(lpFile, lpDirectory, sExePath)
             sExePath = Left$(sExePath, InStr(sExePath, Chr$(0)) - 1)
            ExePath = sExePath
          End Function
          
          Sub Test_ExePath()
             MsgBox ExePath(ThisWorkbook.FullName)
          End Sub

          Comment


          • #6
            Re: Print a PDF file using VBA

            Hi MrRedli and Kenneth

            Both of them works perfectly. Thank you to you both.

            Now I can see that both of the codes print the Pdf file in A4. Is it possible to have the code to prnt A3 as the PDF file are savede in??

            Thank you in advance.

            Comment


            • #7
              Re: Print a PDF file using VBA

              Clone your printer and set the print properties to suit and set that printer as the default. We could show you how to switch printers.

              Comment


              • #8
                Re: Print a PDF file using VBA

                Hi Kenneth

                I though so that Excel can't control Adope's printing menu. I have made the changes in Adope and all works perfect now. Thanks for your time and your help.

                Comment


                • #9


                  Hi,

                  I found the variant that worked for me too as I was about to print to a specific printer: I substituted print with printto as the verb of ShellExecute. Also I had to give a specific printer name in the 4th parameter after the filename. The topic was on MrExcel webpage too: Print a pdf file from VBA?

                  Comment

                  Working...
                  X