Using Excel VBA to add text to a PDF file

[pingme89can]

I am trying to open a PDF file with Excel 2013 by using VBA and then adding text to specific locations on the PDF.

I have Adobe Acrobat 9 Pro installed on a Windows 7 PC with Office 2013.

Thanks in advance.

[lrosenth]

Unfortunately, Adobe no longer supports Acrobat 9 (and hasn’t for many years now)

[pingme89can]

I am stuck using Acrobat 9 Pro. Does this mean it can't be done?

[lrosenth]

It just means that you are on your own – sorry.

[Karl Heinz Kremer · Accepted solution]

@lrosenth is certainly right about Acrobat 9 no longer being supported by Adobe (neither is Acrobat X), but that does not mean that you are on your own. This forum is frequented mostly by users of Adobe's applications, and that means we don't care much about Adobe's support policy.

The good news is that whatever works with Acrobat DC and VBA should also work with Acrobat 9 (at least regarding the task you want to perform).

The bad news is that there is no functionality in the IAC (Interapplication Communication) API that would allow you to create static PDF content outright. If you want to do that, you need to use the Acrobat plug-in interface and create a plug-in (that requires C/C++). You can however create form fields and annotations (which are dynamic content), and then flatten this dynamic content to convert it to static PDF content. All this can be done using a combination of native IAC functionality and functionality from the JavaScript API that you access via the JSObject. See here for an example of how to use the  the JSObject:  Adobe Acrobat and VBA - An Introduction - KHKonsulting LLC

The only problem with this approach is that if your document already has some dynamic content, you would need to perform a selective flattening, and that complicates things quite a bit.

Take a look at these JavaScript methods:

Doc.addField() - Add a form field: Acrobat DC SDK Documentation

Doc.addAnnot() - Add an annotation: Acrobat DC SDK Documentation

Doc.flattenPage() - flatten one or more pages: Acrobat DC SDK Documentation 

[pingme89can]

Karl,

Thank you very much. Your posting was most informative. I was able to successfully place text in multiple places on the PDF. I was even able place the same text on all pages of the PDF. However, I do have one more issue that I am not sure how to do. I need to add a Stamp on every page of the same PDF document. I only have one Stamp created but it is very large and I need not only to place a stamp on every page, I also need to scale the stamp down by 80% or so. Can you help me solve this issue? This is also done with VBA for Excel.

[Karl Heinz Kremer]

The way I usually approach a problem like this is to solve it first in JavaScript. You can add stamps the same way you add any other annotation using the Doc.addAnnot() method (Acrobat DC SDK Documentation ) - you would first add the annotation, and then in a potential second step modify it's properties. When placing annotations, it's important to take any potential page rotation into account (see here for more information: https://acrobatusers.com/tutorials/auto_placement_annotations One of the annotation properties is the "rect", meaning the rectangle that outlines the annotation, you use that to specify it's size.

Once you've figured out how to do this in JavaScript, you can then work on translating the steps into VB/VBA using the JSObject.

[pingme89can]

Thanks again. I was able to add an Annot to the exact place and size I want but I can't get it to display the signature stamp I want.

This what I have so far:

            Set annot = jso.AddAnnot

            Set props = annot.getprops

                props.page = j

                props.name = "Stamp Name"

                props.Type = "stamp"

                props.rect = recter

                props.Width = 1#

                props.strokeColor = jso.Color.transparent

                props.page = j

                annot.setProps props

Please note that j is an integer that I used for looping to add to every page.

[pingme89can]

Sorry I forgot to add this:

            Set jso = pdDoc.GetJSObject

            recter(0) = 340  ' x lower left

            recter(1) = 35  ' y lower left

            recter(2) = 440 ' x upper right

            recter(3) = 55   ' y upper right

            Set annot = jso.AddAnnot

            Set props = annot.getprops

                props.page = j

                props.name = "Stamp Name"

                props.Type = "stamp"

                props.rect = recter

                props.Width = 1#

                props.strokeColor = jso.Color.transparent

                props.page = j

                annot.setProps props

[pingme89can]

I can only get a box with nothing displayed with the above code.

[lrosenth]

You can’t change the type after application – you need to do it all during the AddAnnot() call.

[Karl Heinz Kremer]

lrosenth​ - you actually can change the type, but it needs to be done in two steps: You first create the annotation, then you retrieve the properties and change the type to e.g. "Stamp" (this is case sensitive, so the "stamp" in the above code snippet will not work). Now you set the properties via setProps, and then get them again. Now it will accept the AP property, but will not honor it when set. If I remember correctly, this used to work in an earlier version (I don't remember which one, but I have such a code snippet stored in my solutions repository, which tells me that I had it working at one time). In the current version of Acrobat, all I can get is the "Draft" stamp, and it will no longer allow me to change that.

pingme89can​ - you may have to resort to calling JavaScript directly via the ExecuteThisJavaScript() method of the AFormAut.App object:

Dim aForm As AFORMAUTLib.AFormApp
Set aForm = CreateObject("AFormAut.App")
aForm.Fields.ExecuteThisJavaScript "var x = this.numPages; app.alert(x);"

You would have to come up with the correct JavaScript code to create your stamp and set all it's properties, and then call that via this method.

[pingme89can]

That is exactly how far I got. I was able to get the "Draft" stamp to show but I was not sure how to reference my personal Signature stamp. Then I tried to fool around with it but couldn't get it to show the signature stamp I wanted.

[Mit5FA0]

Have you solved the problem in the meantime? I have just the same. I manage to create a signature field, but the Javascript call to add the individual signature does not result in an error, but no change either. The field remains empty.

[lrosenth]

Correct – you can COPY the properties but you can’t set them all directly.

[Parej]

Hi, 

Can you pls share the code that you used to add stamp to pdf using VBA.

Thanks in advance.