Avatar of rogerdjr
rogerdjr
Flag for United States of America asked on

Printing a PDF file using Access vba

I added this section of code to a function that adds footnotes to individual acrobat files and it does not work printing the files

Can anybody help?

Added --------------------------------------------------------------------------
    If DoYouWantToSendToPrinter = 6 Then
        Dim appPDF As String
        'The PDf file you want to open
        'Check to see if the file is open already
       
        If Not FileLocked(DestPath & DestFileName) Then
            Documents.Open DestPath & DestFileName
        End If
   
        'Now that the file is open you can print it
   
        'first set the variable to the Adobe program on your computer
   
        appPDF = "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe"
   
        'Now print the file
   
        RetVal = Shell(appPDF & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0)
   
    Else
        MsgBox "no print"
    End If

--------------------------------------------------------------------

(E) Function with added print section --------------------------------------------------------------------------
Public Function AddPageNumbers(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer, ByVal FontSize As Integer, ByVal FontColor As String) As String '07-25-2011, ByVal DoYouWantToInclPathInFtr As Integer) As String
'11-11-2011 Public Function AddPageNumbers(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer) As String '07-25-2011, ByVal DoYouWantToInclPathInFtr As Integer) As String

    Dim ex1 As String
    Dim App As Object, AVDoc As Object, AcroPDDoc As Object, AForm As Object
    Dim Ret As Long
    Dim sString As String * 255
    Dim PdfPrint As String, numPages As Integer
       
    Dim sfile As String
    Dim sText As String
    Dim iFileNum As Integer
   
    Dim FileName As String '06-18-2011
   
    Set App = CreateObject("Acroexch.app")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    Set AForm = CreateObject("AFormAut.App") 'from AFormAPI
   
    If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"
    If Right(DestPath, 1) <> "\" Then DestPath = SourcePath & "\"
   
    'FileName = SourcePath & SourceFileName '06-18-2011 - doesn't like symbols in the path either ":" or "\" - need to research
    FileName = SourceFileName '06-18-2011
    booleanresult = AVDoc.Open(SourcePath & SourceFileName, "")
   
    If booleanresult = True Then
        App.Show
        Set AcroPDDoc = AVDoc.GetPDDoc
       
         'Found Code at this web page https://forums.adobe.com/thread/721676?start=0&tstart=0
         'Java Script manual at http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/js_api_reference.pdf
         'Expert Exchange Question at https://www.experts-exchange.com/questions/28569754/Editing-an-Acrobat-PDF-file-to-add-page-footers-from-Access-2013.html
            ex1 = "  // Set Footer PageNo centered  " & vbLf & "  var Box2Width = 500  " & vbLf & "  for (var p = 0; p < this.numPages; p++)   " & vbLf & "   {   " & vbLf & "    var aRect = this.getPageBox(""Crop"",p);  " & vbLf & "    var TotWidth = aRect[2] - aRect[0]  " & vbLf & "     {  var bStart=(TotWidth/2)-(Box2Width/2)  " & vbLf & "         var bEnd=((TotWidth/2)+(Box2Width/2))  " & vbLf & "         var fp = this.addField(String(""xftPage""+p+1), ""text"", p, [bStart,30,bEnd,15]);   " & vbLf & "         fp.value = """
            ex1 = ex1 & FileName & " -- " & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " " & Hour(Now) & ":" & Minute(Now)
            ex1 = ex1 & " -- Page: "" + String(p+1)+ ""/"" + this.numPages;  " & vbLf & "         fp.textSize=" & FontSize & "; fp.textcolor = color.red; fp.readonly = true;  " & vbLf & "         fp.alignment=""left"";  " & vbLf & "     }  " & vbLf & "   }  "
        AForm.Fields.ExecuteThisJavaScript ex1
    End If
   
   
    On Error Resume Next
   
    AcroPDDoc.Save 1, DestPath & DestFileName
   
    numPages = AcroPDDoc.GetNumPages()
   
        sfile = DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
        sText = IIf(FileNo < 10, "00", IIf(FileNo < 100, "0", "")) & FileNo & " --- " & "Printed " & MaxNoPgs & " of " & IIf(numPages < 10, "00", IIf(numPages < 100, "0", "")) & numPages & " --- " & SourcePath & SourceFileName & " --- " & DestPath & DestFileName
       
        If FileNo = 1 Then Kill DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
       
        iFileNum = FreeFile
        Open sfile For Append As iFileNum
        Write #iFileNum, sText
        Close #iFileNum
   
    If MaxNoPgs >= numPages Then
        MaxNoPgs = numPages
    End If
   
   
    AcroPDDoc.Close
    AVDoc.Close (True)
    App.Exit
   
    Ret = FindWindowWild(0, "Adobe*")
    Call GetWindowText(Ret, sString, 255)
   
    Ret = FindWindow(vbNullString, sString)
   
    PostMessage Ret, WM_CLOSE, CLng(0), CLng(0)

    If DoYouWantToSendToPrinter = 6 Then
        Dim appPDF As String
        'The PDf file you want to open
        'Check to see if the file is open already
       
        If Not FileLocked(DestPath & DestFileName) Then
            Documents.Open DestPath & DestFileName
        End If
   
        'Now that the file is open you can print it
   
        'first set the variable to the Adobe program on your computer
   
        appPDF = "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe"
   
        'Now print the file
   
        RetVal = Shell(appPDF & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0)
   
    Else
        MsgBox "no print"
    End If

    Set AcroPDDoc = Nothing
    Set AVDoc = Nothing
    Set App = Nothing
End Function
Microsoft AccessAdobe Acrobat

Avatar of undefined
Last Comment
rogerdjr

8/22/2022 - Mon
Nick67

You and I have fought with this code before.  First step is to turn off
On error Resume next
and see what's really going bang!

Looking though, you go to all the trouble to close the file and kill Acrobat, only to Shell out and try to print it.  Why?  Why not print the document BEFORE closing & killing Acrobat?
rogerdjr

ASKER
I edited the code as shown below - took your suggestions and simplified the process a bit:

it stops at RetVal = Shell(App & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0) and gives an error message

run-time error 438 object doesn't support this property or method

So based on an internet search I'm guessing that the problem is that I'm using Set App = CreateObject("Acroexch.app") and should use a different App that supports printing

The "executable files" I have for the acrobat version I am using are

C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe
C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe

After some internet search I tried
  AcroExchPDDoc.PrintOut
in place of
  RetVal = Shell(App & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0)
and got the same error message

I also tried substituting
     Call AVDoc.PrintPages(0, numPages -1, 1, True, True)
in place of
 RetVal = Shell(App & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0)
did not get an error message but the document did not print (no pdf in my Acrobat default print path)

A couple of web pages I looked at in my search
  http://www.tek-tips.com/viewthread.cfm?qid=1186576
  http://www.planetpdf.com/forumarchive/66844.asp

I'm stumped - Any ideas?
 
Revised code --------------------------------------------------------------
Public Function AddPageNumbers(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer, ByVal FontSize As Integer, ByVal FontColor As String) As String '07-25-2011, ByVal DoYouWantToInclPathInFtr As Integer) As String

    Dim ex1 As String
    Dim App As Object, AVDoc As Object, AcroPDDoc As Object, AForm As Object
    Dim Ret As Long
    Dim sString As String * 255
    Dim PdfPrint As String, numPages As Integer
       
    Dim sfile As String
    Dim sText As String
    Dim iFileNum As Integer
   
    Dim FileName As String '06-18-2011
   
    Set App = CreateObject("Acroexch.app")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    Set AForm = CreateObject("AFormAut.App") 'from AFormAPI
   
    If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"
    If Right(DestPath, 1) <> "\" Then DestPath = SourcePath & "\"
   
    'FileName = SourcePath & SourceFileName '06-18-2011 - doesn't like symbols in the path either ":" or "\" - need to research
    FileName = SourceFileName '06-18-2011
    booleanresult = AVDoc.Open(SourcePath & SourceFileName, "")
   
    If booleanresult = True Then
        App.Show
        Set AcroPDDoc = AVDoc.GetPDDoc
       
         'Found Code at this web page https://forums.adobe.com/thread/721676?start=0&tstart=0
         'Java Script manual at http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/js_api_reference.pdf
         'Expert Exchange Question at https://www.experts-exchange.com/questions/28569754/Editing-an-Acrobat-PDF-file-to-add-page-footers-from-Access-2013.html
            ex1 = "  // Set Footer PageNo centered  " & vbLf & "  var Box2Width = 500  " & vbLf & "  for (var p = 0; p < this.numPages; p++)   " & vbLf & "   {   " & vbLf & "    var aRect = this.getPageBox(""Crop"",p);  " & vbLf & "    var TotWidth = aRect[2] - aRect[0]  " & vbLf & "     {  var bStart=(TotWidth/2)-(Box2Width/2)  " & vbLf & "         var bEnd=((TotWidth/2)+(Box2Width/2))  " & vbLf & "         var fp = this.addField(String(""xftPage""+p+1), ""text"", p, [bStart,30,bEnd,15]);   " & vbLf & "         fp.value = """
            ex1 = ex1 & FileName & " -- " & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " " & Hour(Now) & ":" & Minute(Now)
            ex1 = ex1 & " -- Page: "" + String(p+1)+ ""/"" + this.numPages;  " & vbLf & "         fp.textSize=" & FontSize & "; fp.textcolor = color.red; fp.readonly = true;  " & vbLf & "         fp.alignment=""left"";  " & vbLf & "     }  " & vbLf & "   }  "
        AForm.Fields.ExecuteThisJavaScript ex1
    End If
   
   
On Error Resume Next
   
    AcroPDDoc.Save 1, DestPath & DestFileName
   
    numPages = AcroPDDoc.GetNumPages()
   
        sfile = DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
        sText = IIf(FileNo < 10, "00", IIf(FileNo < 100, "0", "")) & FileNo & " --- " & "Printed " & MaxNoPgs & " of " & IIf(numPages < 10, "00", IIf(numPages < 100, "0", "")) & numPages & " --- " & SourcePath & SourceFileName & " --- " & DestPath & DestFileName
       
        If FileNo = 1 Then Kill DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
       
        iFileNum = FreeFile
        Open sfile For Append As iFileNum
        Write #iFileNum, sText
        Close #iFileNum
   
    If MaxNoPgs >= numPages Then
        MaxNoPgs = numPages
    End If
On Error GoTo 0
   
    If DoYouWantToSendToPrinter = 6 Then
   
        RetVal = Shell(App & "/P" & Chr(34) & DestPath & DestFileName & Chr(34), 0)
   
    Else
        MsgBox "no print"
    End If

    Set AcroPDDoc = Nothing
    Set AVDoc = Nothing
    Set App = Nothing
End Function
Nick67

It wouldn't be just a bloody space in the command line would it?
"/P" is concatenated.
The end result will need a space between the QUOTE SURROUNDED executable -- if the path has spaces -- and the slash P.

Shell is the same as the Windows UI  run line in Statt Menu.
Can you successfully compose a string that will Start |Run... and print a PDF.
It's then a matter of getting that correct in VBA.
Your help has saved me hundreds of hours of internet surfing.
fblack61
rogerdjr

ASKER
tried         RetVal = Shell(App & "/P " & """" & DestPath & DestFileName & """", 0) - no luck

went to Run menu and entered

 C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe /p "j:\0\004113-Bid Form.pdf"

 "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe" /p "j:\0\004113-Bid Form.pdf"

and in both cases it printed to my default printer

also tried running the code with this change

        appPDF = """" & "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe" & """"

        RetVal = Shell(appPDF & "/P " & """" & DestPath & DestFileName & """", 0)

and in both cases it printed to my default printer (HP LaserJet) - looks like I need a sub routine to set the default printer and reset it?

went to Run menu and entered

 acroexch.app /p "j:\0\004113-Bid Form.pdf"
 acroexch.exe /p "j:\0\004113-Bid Form.pdf"

searched for acroexch*.* on C drive could not find it
Nick67

and in both cases it printed to my default printer (HP LaserJet) - looks like I need a sub routine to set the default printer and reset it?
Yes.
The /P will quick print it, which mean the default printer

searched for acroexch*.* on C drive could not find it
Nor will you.  This is the object call
Set App = CreateObject("Acroexch.app")
For Excel it would be
Set App = CreateObject("Excel.Application")
but you won't find Excel.Application either

But now, from your last PDF Q you had

Public Function PrintPdfFile(ByVal PdfPath As String, ByVal PdfFileNm As String, ByVal PdfFirstPageToPrint As Integer, ByVal PdfMaxPagesToPrint As Integer) As String
     Dim PdfPathAndFile As String
     Dim AVDoc As Acrobat.AcroAVDoc
     Dim retcd As Integer
     
     If Right(PdfPath, 1) = "\" Then
         PdfPathAndFile = PdfPath & PdfFileNm
     Else
         PdfPathAndFile = PdfPath & "\" & PdfFileNm
     End If
     
     Set AVDoc = CreateObject("AcroExch.AVDoc")
     
     retcd = AVDoc.Open(PdfPathAndFile, "Title")    ' pathname of pdf file to be opened
     
     If retcd <> -1 Then
 '        MsgBox ("Failed to open PDF document " & PdfPathAndFile)
         Exit Function
     End If

     'AVDoc.PrintPages(firstPage, numPages, 2, true, true)
     If PdfMaxPagesToPrint >= 1 Then PdfMaxPagesToPrint = PdfMaxPagesToPrint - 1  'allows for the fact that the first page is page 0
     If PdfFirstPageToPrint >= 1 Then PdfFirstPageToPrint = PdfFirstPageToPrint - 1 'allows for the fact that the first page is page 0
     
     'PrintPagesSilent(nFirstPage As Long, nLastPage As Long, nPSLevel As Long, bBinaryOk As Long, bShrinkToFit As Long) As Boolean
         'Params 1 & 2 are: Print from page __ to page __
         'Param 3: Must be either 1 or 2. 1 generates Level 1 PostScript code. 2 generates Level 2 PostScript code. (You'll have to test in your own environment, but fortunately the list is quite short.)
         'Param 4: True, indicates the PostScript code may contain binary data. False, indicates binary data is encoded into an ASCII format.
         'Param 5: True, shrink (if necessary). False, print actual size; pages may appear clipped.

     retcd = AVDoc.PrintPagesSilent(PdfFirstPageToPrint, PdfMaxPagesToPrint, 0, 1, True)         ' this prints the number of pages specified by PdfMaxPagesToPrint (i.e. page 0)
     '11-23-2011 retcd = AVDoc.PrintPagesSilent(PdfFirstPageToPrint, PdfMaxPagesToPrint, 0, 1, 1)         ' this prints the number of pages specified by PdfMaxPagesToPrint (i.e. page 0)
     'retcd = AVDoc.PrintPages(PdfFirstPageToPrint, PdfMaxPagesToPrint, 0, 1, 1)         ' this prints the number of pages specified by PdfMaxPagesToPrint (i.e. page 0)
     'avDoc.PrintPagesSilent(0, (numPages - 1), PSLevel, BinaryOk, ShrinkToFit);


     AVDoc.Close (1)

 End Function 

Open in new window


Did it not work as you expected?
ASKER CERTIFIED SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rogerdjr

ASKER
Great Solution

This is the code I ended up with - works great

Thanks a million

----------------------------------------------------------
Public Function AddPageNumbers(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer, ByVal FontSize As Integer, ByVal FontColor As String) As String '07-25-2011, ByVal DoYouWantToInclPathInFtr As Integer) As String
'11-11-2011 Public Function AddPageNumbers(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer) As String '07-25-2011, ByVal DoYouWantToInclPathInFtr As Integer) As String

    Dim ex1 As String
    Dim App As Object, AVDoc As Object, AcroPDDoc As Object, AForm As Object
    Dim Ret As Long
    Dim sString As String * 255
    Dim PdfPrint As String, numPages As Integer
       
    Dim sfile As String
    Dim sText As String
    Dim iFileNum As Integer
   
    Dim FileName As String '06-18-2011
   
    Set App = CreateObject("Acroexch.app")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    Set AForm = CreateObject("AFormAut.App") 'from AFormAPI
   
    If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"
    If Right(DestPath, 1) <> "\" Then DestPath = SourcePath & "\"
   
    'FileName = SourcePath & SourceFileName '06-18-2011 - doesn't like symbols in the path either ":" or "\" - need to research
    FileName = SourceFileName '06-18-2011
    booleanresult = AVDoc.Open(SourcePath & SourceFileName, "")
   
    If booleanresult = True Then
'12-19-2014        App.Show
        Set AcroPDDoc = AVDoc.GetPDDoc
       
         'Found Code at this web page https://forums.adobe.com/thread/721676?start=0&tstart=0
         'Java Script manual at http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/js_api_reference.pdf
         'Expert Exchange Question at https://www.experts-exchange.com/questions/28569754/Editing-an-Acrobat-PDF-file-to-add-page-footers-from-Access-2013.html
            ex1 = "  // Set Footer PageNo centered  " & vbLf & "  var Box2Width = 500  " & vbLf & "  for (var p = 0; p < this.numPages; p++)   " & vbLf & "   {   " & vbLf & "    var aRect = this.getPageBox(""Crop"",p);  " & vbLf & "    var TotWidth = aRect[2] - aRect[0]  " & vbLf & "     {  var bStart=(TotWidth/2)-(Box2Width/2)  " & vbLf & "         var bEnd=((TotWidth/2)+(Box2Width/2))  " & vbLf & "         var fp = this.addField(String(""xftPage""+p+1), ""text"", p, [bStart,30,bEnd,15]);   " & vbLf & "         fp.value = """
            ex1 = ex1 & FileName & " -- " & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " " & Hour(Now) & ":" & Minute(Now)
            ex1 = ex1 & " -- Page: "" + String(p+1)+ ""/"" + this.numPages;  " & vbLf & "         fp.textSize=" & FontSize & "; fp.textcolor = color.red; fp.readonly = true;  " & vbLf & "         fp.alignment=""left"";  " & vbLf & "     }  " & vbLf & "   }  "
        AForm.Fields.ExecuteThisJavaScript ex1
    End If
   
   
    On Error Resume Next
   
    AcroPDDoc.Save 1, DestPath & DestFileName
   
    numPages = AcroPDDoc.GetNumPages()
   
        sfile = DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
        sText = IIf(FileNo < 10, "00", IIf(FileNo < 100, "0", "")) & FileNo & " --- " & "Printed " & MaxNoPgs & " of " & IIf(numPages < 10, "00", IIf(numPages < 100, "0", "")) & numPages & " --- " & SourcePath & SourceFileName & " --- " & DestPath & DestFileName
       
        If FileNo = 1 Then Kill DestPath & "Files Printed " & Format$(Now, "YYMMDD") & ".txt"
       
        iFileNum = FreeFile
        Open sfile For Append As iFileNum
        Write #iFileNum, sText
        Close #iFileNum
   
    If MaxNoPgs >= numPages Then
        MaxNoPgs = numPages
    End If
On Error GoTo 0
   
'12-14-2014 A couple of web pages I looked at in my search
'12-14-2014   http://www.tek-tips.com/viewthread.cfm?qid=1186576
'12-14-2014   http://www.planetpdf.com/forumarchive/66844.asp


'12-14-2014    AcroPDDoc.Close
'12-14-2014    AVDoc.Close (True)
'12-14-2014    App.Exit
'12-14-2014
'12-14-2014    Ret = FindWindowWild(0, "Adobe*")
'12-14-2014    Call GetWindowText(Ret, sString, 255)
'12-14-2014
'12-14-2014    Ret = FindWindow(vbNullString, sString)
'12-14-2014
'12-14-2014    PostMessage Ret, WM_CLOSE, CLng(0), CLng(0)

    AcroPDDoc.Close
    AVDoc.Close (True)
    App.Exit
   
    Set AcroPDDoc = Nothing
    Set AVDoc = Nothing
    Set App = Nothing


    If DoYouWantToSendToPrinter = 6 Then
        appPDF = """" & "C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Acrobat.exe" & """"
        RetVal = Shell(appPDF & " /t /h /s /o " & """" & DestPath & DestFileName & """" & " Adobe PDF", 0)
    Else
        MsgBox "no print"
    End If

End Function
âš¡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.