QU: Automate PDF Reports Batch Processing using MS Access 2003

[KathleenA]

Objective: To open a pdf report from Access03 module, use SendKeys to paste in a predetermined, client specific password for (already set-up) secure email sending. Close. Loop to next report.

Table contains client code, password. Match reports in folder by Client Code and Paste in strRptPath for correct report selection done.

Issue: Successfully launching Adobe Acrobat, BUT lose focus immediately and sendkeys ends up activating Access03 again.

Ideas? Help says the Shell() function returns a Task ID (Double) but how to capture this double value? (for continued focus on Acrobat App.)

This segment thus far:
Private Sub cmdAcrobat()

Dim stAppName, strPath As String
strPath = "C:\Reports\REPORT_TEST_CLIENT91.pdf"
stAppName = "C:\Program Files\Adobe\Acrobat 8.0\Acrobat\Acrobat.exe"
Call Shell(stAppName, 0)
[FOCUS RETURNS TO ACCESS03 HERE]
SendKeys "{F10}"
SendKeys "%F%O", True
SendKeys "^V" & strPath, True

Thanks, KA

 

[jrbarnett]

Untested, but the idea is basically:

get the result of the Shell statement into a variant variable, then pass that into AppActivate.

   Dim stAppName, strPath As String
   Dim varResult as Variant
    strPath = "C:\Reports\REPORT_TEST_CLIENT91.pdf"
    stAppName = "C:\Program Files\Adobe\Acrobat 8.0\Acrobat\Acrobat.exe"
    varResult = Shell(stAppName)
    AppActivate (varResult, 1)
' [FOCUS RETURNS TO ACCESS03 HERE]
        SendKeys "{F10}"
        SendKeys "%F%O", True
        SendKeys "^V" & strPath, True

John

 

[KathleenA]

Thank you for the suggestion, however, it still creates an error message. "Run-time error #5, Invalid Procedure Call.

 

[KathleenA]

So I tried several approaches, and had success with

Dim stAppName, strPath As String
Dim varResult as Variant
strPath = "C:\Reports\REPORT_TEST_CLIENT91.pdf"

Application.FollowHyperlink strPath
SendKeys etc...