Using Excel VBA to add text to a PDF file

Forum|Forum|8 years ago
June 30, 2017
2 replies
27964 views

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.

This topic has been closed for replies.

Correct answer Karl Heinz Kremer

@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

Karl Heinz KremerCorrect answer

Adobe Expert

@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

P

pingme89canAuthor

Participating Frequently

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.

P

pingme89canAuthor

Participating Frequently

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.

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.