Using Shell method to "Print" pdf and then pausing until pdf compl

[sjlevine34]

From with MS Access 2002, I am needing to convert various documents to pdf
format and then combine them into a single document.

Where I am having a problem is getting the VBA script to pause until
preparation of pdf is complete. Once this is done, execution is to continue
using the various methods of Adobe Acrobat SDK 6.0 to annotate the pdf and
merge it into the master pdf.

The documents that I need to convert to pdf's include those in Word, Excel,
and visio as well as other pdf's. The pdf's can just be copied. The others
have to be "printed" to an Adobe Distiller type printer. I need to be able
to pause the VBA script until these actions are complete.

I have been using the scripting.filesystemobject for copying the files. For
printing, I have used both ShellExecute and ShellExecuteEx, because these
print the documents using the applications to which they are linked in my
Windows XP operating system. I went to ShellExecuteEx because I read
documentation that VBA does not pause while ShellExecute executes. Per some
of the documents I read, I tried to use getsingleobject method to pause the
VBA script until pdf preparation was complete, but this does not seem to work:

My code is as follows:

Option Compare Database
Option Explicit

Public fs As Object

Private objDocument As Object, lpt1 As Printer, dummy, gstrOldDefaultPrinter
As String
Private cncurrent As ADODB.Connection, strTempFile As String
Private Type SHELLEXECUTEINFO
cbSize As Long
fMask As Long
hwnd As Long
lpVerb As String
lpFile As String
lpParameters As String
lpDirectory As String
nShow As Long
hInstApp As Long
lpIDList As Long
lpClass As String
hkeyClass As Long
dwHotKey As Long
hIcon As Long
hProcess As Long
End Type

Declare Function ShellExecuteEx Lib "shell32.dll" Alias _
"ShellExecuteExA" (shxInfo As SHELLEXECUTEINFO) As Long

Private Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal
hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const SW_HIDE = 0
Private Const SW_SHOW = 1
Private Const SEE_MASK_INVOKEIDLIST = &HC
Private Const SEE_MASK_NOCLOSEPROCESS = &H40
Private Const SEE_MASK_FLAG_NO_UI = &H400
Private Const INFINITE = &HFFFF
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Public Function PrintToPDF(strFullPath As String) As Long
Dim llngRetCode As Long, lstrExt As String, lstrTempPath As String,
lstrTempFile As String
Dim lstrMyDocuments As String, lstrMyDocFile As String, llngWaitCode,
datStart As Date
Dim lshexInfo As SHELLEXECUTEINFO

Set fs = CreateObject("scripting.filesystemobject")
lstrExt = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath,
"."))
lstrTempPath = PathWorkSpaceFolder
lstrTempFile = lstrTempPath & "\Temp." & Trim(lstrExt)
llngRetCode = fs.copyfile(strFullPath, lstrTempFile, True)

With lshexInfo
.cbSize = Len(lshexInfo)
.fMask = SEE_MASK_NOCLOSEPROCESS
.hwnd = hWndAccessApp
.lpVerb = "PRINT"
.lpFile = lstrTempFile
.lpParameters = vbNullString
.lpDirectory = lstrTempPath
.nShow = SW_SHOW
End With

llngRetCode = ShellExecuteEx(lshexInfo)
WaitForSingleObject lshexInfo.hProcess, INFINITE
If llngRetCode > 0 Then
If lstrExt = "vsd" Then
strTempFile = lstrTempPath & "\Visio-Temp.pdf"
Else
strTempFile = lstrTempPath & "\temp.pdf"
End If
MsgBox fs.FileExists(strTempFile)
MsgBox fs.FileExists(strTempFile)
End If

End Function

The result of this code's execution should be that both message boxes should
display "True". Instead, the first messagebox displays "False" meaning that
it is reached before the pdf is prepared.

I would appreciate the help as this problem appears to be a showstopper for
completing a report that management very anxious to have.

sjl

 

[Allen Browne]

The code in this link will shell and wait:
http://www.mvps.org/access/api/api0004.htm

 

[sjlevine34]

How would I get CreateProcessA to do what I am having ShellExecuteEx do,
which is to automatically print a file using its associated application?

 

[Tony_VBACoder]

I stumbled upon this post, as I am having the same issue. However, this link
that Allen listed, will only work for the application, such as Notepad.exe,
not the individual file. And it will wait until you've closed that
application (exited out of Notepad.exe) before any code will continue to
execute after the call to this API. It will not wait until say, a file has
finished loading in it's associated program.

I am in need of finding the right API that will run the ShellExecute on a
file, not an application such as Notepad.exe, and then WAIT for the file to
load in it's associated program. For instance, I am needing to call the
ShellExecute to launch a PDF document (ie: MyPDF.pdf) and then I need to call
another API that will wait until this file (MyPDF.pdf) has finished loading,
and then have my VBA code continue. I am not looking to exit out of Acrobat
or Acrobat Reader once the PDF has fully loaded - the user must still have
the PDF loaded on the screen.