G
Guest
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
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