Adding page numbers to pdf through VBA and Acrobat IAC

Question

I am trying to do the following thing from Excel vba:

1. Export certain worksheets to pdf
2. Take an existing pdf document and insert it in the newly generated pdf at a specific place (not necessarily at the end or at the beginning)
3. Number the pages of the merged pdf, omitting two title pages

I already figured out the first step. For the second and third step, I have Adobe Acrobat XI Pro at my disposal. Since I want to do this in one go from vba, I have downloaded the Acrobat SDK. From some quick Googling, I think I should be able to figure out the second step now, using the IAC, but the third step (oddly) seems the most difficult. Any suggestions would be welcome.

Best, NiH

Launcelot

Answer 1

In the meantime, I found a solution for adding page numbers. For anyone who might be interested, here's an example of how it can be done:

Sub addPageNumbers()

    Dim acroApp As Acrobat.acroApp
    Dim myDocument As Acrobat.AcroPDDoc
    Dim jso As Object

    Dim strPath As String
    Dim strFileName As String
    Dim intPages As Integer
    Dim i As Integer

    Set acroApp = CreateObject("AcroExch.App")
    Set myDocument = CreateObject("AcroExch.PDDOc")

    strPath = "C:\"
    strFileName = "myDoc.pdf"

    'Open file and load JSObject
    Set myDocument = CreateObject("AcroExch.PDDOc")
    myDocument.Open (strPath & strFileName)
    Set jso = myDocument.GetJSObject

    ' get number of pages
    intPages = myDocument.GetNumPages

    'Write page numbers to all pages
    For i = 1 To intPages
        jso.addWatermarkFromText _
            cText:=Str(i) & "  ", _
            nTextAlign:=1, _
            nHorizAlign:=2, _
            nVertAlign:=4, _
            nStart:=i - 1, _
            nEnd:=i - 1
    Next i

    'Save document
    Call myDocument.Save(1, strPath & strFileName)

    'Clean up
    Set jso = Nothing
    Call acroApp.CloseAllDocs
    Set myDocument = Nothing
    Call acroApp.Exit
    Set acroApp = Nothing

End Sub

Keep in mind that you need to have Acrobat (not only the reader) installed on your computer, and the reference to Acrobat has to be enabled in the vba editor.

I did not add error handling; obviously you should.

More info on the addwatermarkFromText method can be found here

Best regards,

NiH

Launcelot

Answer 2

Here another method to do it. I use the add field method from acrobat js. The "ExecuteThisJavaScript" method has the advantage that you can use js without translation to js-object.

The following example - I published somewhere already - add Date, filename and pageNo as footer to a pdf. It's written in VBS but can also used as vba without changes.

Best regards, Reinhard

File = "D:\Test.pdf"

Set App = CreateObject("Acroexch.app")      '//start acrobat
app.show                                    '//show Acrobat or comment out for hidden mode
Set AVDoc = CreateObject("AcroExch.AVDoc")
Set AForm = CreateObject("AFormAut.App")   '//get AFormAPI to execute js later

If AVDoc.Open(File,"") Then
    '//write JS-Code on a variable
    Ex = "  //  set Date, filename and PageNo as footer "&vbLF _
      & "  var Box2Width = 50  "&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 = ""Page: "" + String(p+1)+ ""/"" + this.numPages;  "&vbLF _
      & "         fp.textSize=6;  fp.readonly = true;  "&vbLF _
      & "         fp.alignment=""center"";  "&vbLF _
      & "     }  "&vbLF _
      & "   }  "
    '//Execute JS-Code
       AForm.Fields.ExecuteThisJavaScript Ex
    msgBox("Done")
end if

Set AVDoc = Nothing
Set APP = Nothing

Baklava

is it possible to access headers and footers directly using acrobat pro in vba, as I note that the output is not in the footer itself. – Houseless