Using Acrobat with MS Access VBA

[tony_bacon]

Using Acrobat 9.5. I'm trying to analyse a problem, so I have this test code:

CODE

Sub testObjects()
Dim i As Boolean
Dim objapp As acrobat.AcroApp
Set objapp = New acrobat.AcroApp
objapp.Show
i = objapp.CloseAllDocs
Debug.Print i
i = objapp.Exit
Debug.Print i
Set objapp = Nothing
End Sub

After Objapp.closealldocs I get i = true (succeeded) but after i=objapp.exit I get i= false (failed), and sure enough Acrobat is still active. How do I get Acrobat to close? This is a bit of test code. In the real app task manager shows acrobat using more and more memory as the app loops through the records.

I suspect I'm missing something obvious here, but what? My experience with Acrobat and Access automation is limited, so be gentle with me!

If this is the wrong forum, please point me to where I ought to go.

[try67]

[Question moved to the Acrobat SDK forum]

[tony_bacon]

Nobody here who can help?

[Karl Heinz Kremer]

If I remember correctly, this has not worked in a long time. You should be able to exit Acrobat (assuming that there are no open and unsaved documents) via this command:

objapp.MenuItemExecute ("Quit")

[tony_bacon]

Thanks for the info. I'll give that a go and see what happens.

[tony_bacon]

OK, here is the full situation - sorry it is a bit long:

The situation is that I have an application that loops through a table getting membership info and using it to generate individualised PDF files that are then attached to emails to the members. It all works, but when I look in Task Manager, Acrobat is occupying more and more memory as the job progresses. This is sort-of OK with my test data, but when I start progressing the full database it is not going to cope.

Here is a mixture of code and pseudocode that illustrates what I am doing.

[CODE}

Sub ProcessEmails()

For each record in the table:

Call GeneratePDF(various member data arguments…)

Next record

Tidy up

End sub

Sub GeneratePDF(member data parameters…)   ‘               This is where the work gets done

Dim AcroApp As Acrobat.CAcroApp

Dim ThePage As Object

Dim TheDoc As Acrobat.CAcroPDDoc

Dim jso As Object

Dim strPDFtemplate As String

Dim CAcroRect As New Acrobat.AcroRect

‘               plus other declarations

Set AcroApp = CreateObject("AcroExch.App")

Set TheDoc = CreateObject("AcroExch.PDDoc")

‘               some setup code, then

          AcroApp.Hide

          TheDoc.Open (strPDFtemplate)

          Set jso = TheDoc.GetJSObject

          Set ThePage = TheDoc.AcquirePage(0)

          jso.addWatermarkFromText "1 January - 31 December " + year, jso.app.Constants.Align.Left, "Arial-Bold", 10, Colarray, 0, 0, True, True, True, jso.app.Constants.Align.Center, jso.app.Constants.Align.Center, 50, 301.5, False, 1, False, 0, 1 'Year

‘           then a whole lot more of the same

‘           To finish:

i = TheDoc.Save(PDSaveFull Or PDSaveCollectGarbage, pubPDFUpdatedFilename)

TheDoc.Close

AcroApp.Exit

Set TheDoc = Nothing

Set AcroApp = Nothing

End Sub

[/CODE]

It would appear that I am not terminating something I should be during this process so Acrobat is not releasing memory.

I have tried all sorts of different things, including putting the instantiation and termination of Acrobat outside the read loop, but everything I have tried either makes no difference or gives me various execution time errors.

I’m not very experienced with Automation and I find the Acrobat documentation fairly opaque, so I’m hoping there is something obvious I’ve missed and that some eagle-eyed forum member will be able to help me.

[Test Screen Name]

You don't seem to have made the suggested change.

[tony_bacon]

Do you mean using Quit? (a slightly more verbose answer would be helpful).

In the sample I just provided you are right - that is an edited version of the "real" code. In my small tests I have used Quit and as far as I can see it has made no difference.

I'l try using Quit in the actual app and see what happens.

[Test Screen Name]

Yes, I meant to use Quit. (It was, after all, the only suggestion anyone had made and your post seemed to suggest it had been ignored).

The key thing which you won't find in any documentation is this: Acrobat is an interactive tool with the ability to do some automation. This is only suitable for VERY LOW VOLUME use. Tests work fine for a dozen, then people assume they can process 50,000 and things never work properly, not even at 500. It just isn't the right tool for the job. However, quitting Acrobat, as you intended to do, is a reasonable approach to making an unsuitable tool just about suit. People will commonly quit every 20 or so.

The trick is how. Try these APIs, then stop the process and check if Acrobat has done as it was asked. Use Task Manager as it may be invisible. If no internal API works you may need to use some messy external API. Or just stop after 20, manually quit, and run the next batch.

[tony_bacon]

Taking on board what you say about volumes, which may be a show-stopper as I have to progress about 400 records (it is the membership of a club I belong to), the relevant bit of code now says:

     TheDoc.Close

      '     AcroApp.Exit

    Set TheDoc = Nothing

    Set ThePage = Nothing

    AcroApp.MenuItemExecute ("Quit")

    Set AcroApp = Nothing

    '     Pause (10)

I appreciate your help.

When I run the code nothing has changed. Task Manager still shows a steady increase in memory usage, and the task never disappears. I even put in the pause for 10 secs  to see if that would make it stop, but no. Looking at the documentation for QUIT, it says it will only do it if there are no open docs (or words to that effect). From that I infer there is something I'm not doing - can you suggest what it might be? The fact that it is not releasing memory also adds weight to that theory.

Acknowledging what you said about volumes and also my sketchy knowledge of the Acrobat API, I would have thought one should be able to open the program, (memory increases), open a document (more memory), save and close the doc (memory decreases) and so on round the loop.

Are my declarations and invocation and release of Acrobat correct? Is there any code I can run that will tell what if anything is still open/active?

[Karl Heinz Kremer]

Acrobat is leaking resources when you run it in a loop as you do. That's why it's not suited for large scale automation. You are correct, the command will not shut down Acrobat if there are open documents. You can use the automation interface to find out if there are indeed documents open - keep in mind that an open document does not necessarily show up on the user interface.

It's possible that you try to close a document, but it will not actually be closed because you've acquired a resource and not released that resource.

Where exactly the limit in regards to how many documents you can process in one loop depends on what exactly you are doing with your documents. Sometimes 20 is too many, and sometimes you can go as high as a few hundred. You would have to figure out what the limit is for you specific case. One way to do that is to write a timestamp to a text file after every document. This way, you can see how long it takes to process one documents, and chances are that this time will get longer and longer. You have to decide when it makes sense to break up the job.

[tony_bacon]

Thanks for the info. Can you give me a clue as to how to use the automation interface to see what is happening? - As I've confessed earlier my understanding of the Acrobat SDK is sketchy to say the least. My Access VBA knowledge is somewhat better, but I'm by no means an expert. I'm a former professional programmer, but my Access is totally self-taught and has never been used in any sort of team/mutual support/learning situation.

Code snippets or pointers to relevant documentation would be extremely helpful, as I find the online Adobe docs fairly opaque.

I'm actually investigating a plan B of creating Word docs instead of PDFs, but I'd like to understand the Acrobat interface better whatever happens.

[Karl Heinz Kremer]

This function gives you the number of open AVDocs - these are the documents that are being shown in Acrobat:

Acrobat DC SDK Documentation

Again, this does not  take "hidden" documents into account.

You could try to close all documents using this function:

Acrobat DC SDK Documentation

All the information you need is in the Acrobat SDK. 

[tony_bacon]

GetNumAVDocs is returning 0 and CloseAllDocs  is making no difference so I think I've probably come to the end of the road unless I go the route of doing it in chunks - which would very much be a last resort. I'll continue with plan B of creating Word docs instead, although that has its own set of compromises.

Thanks everyone for the help and advice.

[lrosenth]

Actually, you won’t be able to get the proper number of open documents due to security concerns.

[tony_bacon]

So how do I address the security issue? What are the security concerns? Is there any other way to establish whether or not I have documents open?

[Test Screen Name]

Security issues are a SECRET. Adobe discovers or is told a weakness, and breaks part of Acrobat so the weakness can't be exploited. But the details are not revealed to help keep users of older software from being a target. This may or may not be necessary (we can't know) but it does seem to be the case that the changes don't always make it into the documentation in a timely fashion or at all. I found this article which may explain why your documents aren't closing, and while it doesn't say so I imagine it has a security reason behind it:

https://answers.acrobatusers.com/Acrobat-Javascript-bug-closeDoc-no-longer-close-the-documents-intro...

[tony_bacon · Accepted solution]

I thought people might be interested to know I've found a work-around to the memory leakage issue. I've inserted a pause every so many records and that seems to be enough to let Acrobat release the memory.

On the development PC a 10 second pause every 50 records (or probably more) is sufficient. but I will have to do some tuning on the target machine to find the optimal numbers there. It means the process is extended, but it is a once a year operation so I don't think that will be a problem. I'm estimating the final elapsed time will be around 15 minutes.

Of course I didn't think of the solution until after I had laboured  to generate Word docs instead, but it now means we can offer the form in PDF or Word format.

Thanks everyone for all the help and suggestions.