Avatar of rogerdjr
Flag for United States of America asked on

Vba function to add footers to PDF files stopped working

I have an application  that adds footers to a pdf - works fine on my desktop but stopped working on my notebook computer - stops at with this error message

Checked VBA references and they are mostly the same

Versions of Acrobat DC are identical

Used to work fine on both machines

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, Optional RotatePageDegrees As Integer, Optional HeaderFooter As String) ', ByVal SupressNoPrintMsg As Boolean) 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, numPages1 As Integer, PageNoVar 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 = DestFileName '06-18-2011
    booleanresult = AVDoc.Open(SourcePath & SourceFileName, "")
    If booleanresult = True Then
        Set AcroPDDoc = AVDoc.GetPDDoc
            If Not IsNull(RotatePageDegrees) Then
             numPages = AcroPDDoc.GetNumPages()
                For PageNoVar = 0 To numPages - 1
                    Dim PDFPage As AcroPDPage
                    Set PDFPage = AcroPDDoc.AcquirePage(PageNoVar)
                    Call PDFPage.SetRotate(0)
                    Call PDFPage.SetRotate(RotatePageDegrees)
            End If
'    MsgBox numPages
    PageNoVar = 0
    While PageNoVar <= numPages
        ex1 = "this.addWatermarkFromText({" & vbLf
    '    ex1 = ex1 & "cText: " & """" & "DRAFT\n\nCOPY" & """" & "," & vbLf
    '    ex1 = ex1 & "for (var p = 0; p < this.numPages; p++)   " & vbLf
        ex1 = ex1 & "cText: " & """" & filename & " -- " & Format(Now(), "mm-dd-yyyy hh:mm") & " " & PageNoVar + 1 & " of " & numPages & """" & "," & vbLf
    '    ex1 = ex1 & """" & " -- Page: "" + String(p+1)+ ""/"" + this.numPages " & """" & "," & vbLf
        ex1 = ex1 & "nTextAlign:app.constants.align.center," & vbLf
        If HeaderFooter = "Header" Then
            ex1 = ex1 & "nVertAlign:app.constants.align.top," & vbLf
            ex1 = ex1 & "nVertAlign:app.constants.align.bottom," & vbLf
        End If
        ex1 = ex1 & "cFont: " & """" & "Helvetica-Bold" & """" & "," & vbLf
        ex1 = ex1 & "nFontSize: " & FontSize & "," & vbLf
        ex1 = ex1 & "aColor: color.red," & vbLf
        ex1 = ex1 & "nStart: " & PageNoVar & "," & vbLf
        ex1 = ex1 & "nOpacity: 0.5" & vbLf
        ex1 = ex1 & "});"
        PageNoVar = PageNoVar + 1
        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
    AVDoc.Close (True)
    Set AcroPDDoc = Nothing
    Set AVDoc = Nothing
    Set App = Nothing
    If DoYouWantToSendToPrinter = 6 Then
        appPDF = """" & "C:\Program Files (x86)\Adobe\Acrobat dc\Acrobat\Acrobat.exe" & """"
        RetVal = Shell(appPDF & " /t /h /s /o " & """" & DestPath & DestFileName & """" & " Adobe PDF", 0)
    End If
End Function

Open in new window

I'm baffled

VBAPDFMicrosoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

What happens when you run a debug compile?

My guess is that the AForm object is not being created (maybe a missing reference to the AFormAut library?) but that's just my gut feeling based on where the error is happening. It may just be a library that somehow got moved or is missing for some reason on the one PC.

Probably a good Guess - How would yo go about trouble shooting?
John Tsioumpris

Wouldn't be easier to add footer info to the Access report and just create the PDF.
Other than that seems that some kind of Registration error.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

The PDFs are documents received from a client or contractor - i use the database to rename them with proper nomenclature and add a header & footer

I actually posed a related question "Will my VBA code for Adobe Acrobat 11 Pro work if I upgrade to Acrobat DC?" asked 9/18/2017 and answered by Joe Winograd on 9/24/2017 (https://www.experts-exchange.com/questions/29057431/Will-my-VBA-code-for-Adobe-Acrobat-11-Pro-work-if-I-upgrade-to-Acrobat-DC.html)

The process has worked fine without a hitch from the to december 2021 - not sure what changed



Also, I should have confirmed - have you double-checked to make sure it's not something specific to the PDF itself? For example, have you tried the process with the EXACT SAME PDF (not just another PDF that -should- be the same) on the desktop?

Yes it is the exact same PDF
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

If you debug and look at the value for AForm and then for AForm.Fields, what does each one show?

not sure how to do that
John Tsioumpris

Put a breakpoint and when the code execution stops you can check the values either via Immediate or adding a Watch
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Sorry - a death in the family has diverted me from resolution - hope to getback to this issue soon

I have been doing some trouble shooting and it appears that it is an issue with Acrobat blocking access from editing and saving pdfs.

May have been from an automated update to acrobat

Everything worked well on both computers before and now two acrobat related functions that used to run in vba are not working

1) - the code provided in the orignal code in the question adds a header & footer to a pdf
2) - opens one pdf and appends several pdfs and saves the combined pdf


add acrobat and java script to references
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.