• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Using Excel VBA to add text to a PDF file

New Here ,
Jun 30, 2017 Jun 30, 2017

Copy link to clipboard

Copied

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.

TOPICS
Acrobat SDK and JavaScript

Views

23.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Jul 03, 2017 Jul 03, 2017

@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

...

Votes

Translate

Translate
Adobe Employee ,
Jun 30, 2017 Jun 30, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 30, 2017 Jun 30, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Employee ,
Jul 02, 2017 Jul 02, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 03, 2017 Jul 03, 2017

Copy link to clipboard

Copied

@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 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 04, 2017 Jul 04, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 07, 2017 Jul 07, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 07, 2017 Jul 07, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 07, 2017 Jul 07, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 07, 2017 Jul 07, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Employee ,
Jul 07, 2017 Jul 07, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 08, 2017 Jul 08, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 08, 2017 Jul 08, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 01, 2020 Dec 01, 2020

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Employee ,
Jul 10, 2017 Jul 10, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 02, 2020 Sep 02, 2020

Copy link to clipboard

Copied

Hi, 

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

 

Thanks in advance.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines