L
Lee Smith
Hello all.
I am here looking for a solution for one of the accountants that works for
our firm. I am hopping some one here as a better understanding of MS Access
and Adobe than I do and can assist me with this problem.
First off let me tell you what the accountant is doing. He has a MS Access
database of several accounts he is tracking. Every day he wants to update
the information of each account then generate reports for all the accounts.
This he has done and it works with no problem. What he is now trying to do
is automate the process. Right now he has every thing working at one click
of a button. The info is gathered, updated and then the report is
generated. After generating the report he is trying to get it to
automatically convert into an Adobe .pdf file then move on to the next
account. This is where the problem occurs. Now if he does these steps
manually it works just fine including the conversion to .pdf format so we
know it can be done. What we can't seem to work out is how to automate the
entire process.
One of the ways he trying right now is using a key stroke script (see script
below). This part of the process only seems to work some of the time. Some
times the .pdf report is made just fine and it goes to next account. Other
times it just hangs on the print screen. With some trouble shooting and
playing with it we have found if he increases the wait time (for report to
be generated) that seems to help some. None the less it still seems to hang
now and then. More so it hangs when we know report is done and ready to be
printed but for what ever reason it hangs on the print screen. If he hits
cancel fast enough it seems to continue on. Again this would work but
defeats the goal of one click it all gets done. Here is the keystroke code
he is using in Access.
If CurrentRecordNumber = 1 Then
SendKeys "%FPA%P%V%L" 'Alt-File, Print, A (to select Adobe as the
printer), Alt-P (propertites), 'Alt-V (set an Adobe property), Alt-L (set
another Adobe property)
Else
SendKeys "^Q" 'CTRL-Q (exit the Adobe
distiller)
SendKeys "%FPA%P%V%L" 'Same as above...
End If
SendKeys "{Enter}" 'Exit the Properties
SendKeys "{Enter}" 'Accept the printer selection
and get to the file name prompt
After failing with the keystroke option we did some more research in MS
Access itself and found the Access automation library. He has checked all
the Adobe related ones and is trying to use the following command.
Function FileToPDF2(strInputPostScript As String, strOutputPDF As String,
strJobOptions As String, _bApplySecurity As Long) As Integer
He has tried working with this and he can get it to run with no errors but
once it is done there no output either. Here is the command he is currently
using. This is the actual command/function he has in access.
FileToPDF2 "Disbursement Gateway Selection", "C:\documents and
settings\owner\desktop\test.pdf", "", 0
Now he was unable to figure out what went in the " " space but knows if he
left it out the function would error out.
So now you know what he is trying to do and the two ways he is trying to get
it done. Any thoughts, suggestions, tips and/or even solutions would be
GREATLY appreciated.
Feel free to email me or just respond to post.
Lee
(e-mail address removed)
I am here looking for a solution for one of the accountants that works for
our firm. I am hopping some one here as a better understanding of MS Access
and Adobe than I do and can assist me with this problem.
First off let me tell you what the accountant is doing. He has a MS Access
database of several accounts he is tracking. Every day he wants to update
the information of each account then generate reports for all the accounts.
This he has done and it works with no problem. What he is now trying to do
is automate the process. Right now he has every thing working at one click
of a button. The info is gathered, updated and then the report is
generated. After generating the report he is trying to get it to
automatically convert into an Adobe .pdf file then move on to the next
account. This is where the problem occurs. Now if he does these steps
manually it works just fine including the conversion to .pdf format so we
know it can be done. What we can't seem to work out is how to automate the
entire process.
One of the ways he trying right now is using a key stroke script (see script
below). This part of the process only seems to work some of the time. Some
times the .pdf report is made just fine and it goes to next account. Other
times it just hangs on the print screen. With some trouble shooting and
playing with it we have found if he increases the wait time (for report to
be generated) that seems to help some. None the less it still seems to hang
now and then. More so it hangs when we know report is done and ready to be
printed but for what ever reason it hangs on the print screen. If he hits
cancel fast enough it seems to continue on. Again this would work but
defeats the goal of one click it all gets done. Here is the keystroke code
he is using in Access.
If CurrentRecordNumber = 1 Then
SendKeys "%FPA%P%V%L" 'Alt-File, Print, A (to select Adobe as the
printer), Alt-P (propertites), 'Alt-V (set an Adobe property), Alt-L (set
another Adobe property)
Else
SendKeys "^Q" 'CTRL-Q (exit the Adobe
distiller)
SendKeys "%FPA%P%V%L" 'Same as above...
End If
SendKeys "{Enter}" 'Exit the Properties
SendKeys "{Enter}" 'Accept the printer selection
and get to the file name prompt
After failing with the keystroke option we did some more research in MS
Access itself and found the Access automation library. He has checked all
the Adobe related ones and is trying to use the following command.
Function FileToPDF2(strInputPostScript As String, strOutputPDF As String,
strJobOptions As String, _bApplySecurity As Long) As Integer
He has tried working with this and he can get it to run with no errors but
once it is done there no output either. Here is the command he is currently
using. This is the actual command/function he has in access.
FileToPDF2 "Disbursement Gateway Selection", "C:\documents and
settings\owner\desktop\test.pdf", "", 0
Now he was unable to figure out what went in the " " space but knows if he
left it out the function would error out.
So now you know what he is trying to do and the two ways he is trying to get
it done. Any thoughts, suggestions, tips and/or even solutions would be
GREATLY appreciated.
Feel free to email me or just respond to post.
Lee
(e-mail address removed)