Access to Adobe .pdf

[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)

 

[Linda Ribbach]

I'd like to piggyback on your email. Similarly, I am emailing individualized reports to 300 people. I have the reports formatted to snapshot, but I'm afraid that many of the people who receive my report may be using a Mac and I don't think Snapshot Viewer is compatible with Mac's, so I wanted to do a sendobj to a pdf format. There is no option for pdf. Is there a way that I can load a reference that will make pdf available? There are only five choices. Is there a way to add another choice?
acFormatDAP
acFormatHTML
acFormatRTF
acFormatTXT
acFormatXLS

Linda

Private Sub Form_Load()
Dim strSQL As String
Dim strQueryName As String
Dim rs As Recordset
Dim qryDef As QueryDef
Dim dbs As Database

'set variable values
Set dbs = CurrentDb

Set rs = dbs.OpenRecordset("tblAreaManSendEmail")

strQueryName = "qryRequestsEmailSend"

rs.MoveFirst

'Delete old query first - we want fresh data!
Do While Not rs.EOF

dbs.QueryDefs.Delete strQueryName

strSQL = "SELECT Distinct tblRequestContractsEmail.Renewal, tblRequestContractsEmail.NoAction, tblRequestContractsEmail.AreaManR, tblRequestContractsEmail.VendorName, tblRequestContractsEmail.AreaCode, tblRequestContractsEmail.[CUFS Area], tblRequestContractsEmail.ESAFNo, tblRequestContractsEmail.ContractNo, tblRequestContractsEmail.[Description of Request], tblRequestContractsEmail.ActionFormApproveDate, tblRequestContractsEmail.[Applicant Name], tblRequestContractsEmail.ContractExpirationDate, tblRequestContractsEmail.Status FROM tblRequestContractsEmail " _
& "WHERE tblRequestContractsEmail.[areamanR] LIKE '" & rs![AreaMan] & "*';"
'Create query definition


Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

'Send Report
DoCmd.SendObject acReport, "rptEmail", "SnapshotFormat(*.snp)", rs![AreaManEmail], "", "", "External Sales", "Please see the attached.If you do not have Snapshot Viewer, go to the following website and download the program. http://www.jcu.edu.au/office/research_office/snapshot.html", False, ""

'Beep

'MsgBox "Your report has been sent", vbOKOnly, ""

rs.MoveNext

Loop

' *** End of Code ***


End Sub

 

[Linda Ribbach]

I've been thinking about your problem and right now all I can see is the print option allows to save to a pdf. Can you use the code I sent you and instead of Sendobj use print object. Set up a list of people's names as the criteria to create the report and have the program loop through the names and save the report where you want it to go.


Linda

 

[Steve Schapel]

Linda,

Yes, that's the idea. You need to have a PDF printer driver installed
on your computer to do this... there are a number of good ones around,
free or cheap, I have found PDF995 from http://www.pdf995.com/ to be
excellent. Then, in the design of the report, under the File|Page Setup
menu, set this PDF driver as the specific printer for the report. Then
you can just use a DoCmd.OpenReport method to output the report to pfd
files.

 

[Guest]

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)

 

[Guest]

Check out pdf995.com, it may be of help to you. On a daily basis I needed to
run and distribute several access reports as .pdf files. This program along
with the code snippet they provide handles the conversion easily, and the
sendobject with a hyperlink path to the location on our server accomplishes
all that I need with one click of a button.

 

[Mark Andrews]

Adobe PDFWriter, win2pdf and various other pdf printer drivers use registry
keys to
control supplying the filename to them so you can avoid the dialogs popping
up and you
do not have to try and write code with sendkeys (a bad idea).

If you need code to make these drivers work we have a code module you can
plug into your database
that makes a pdf file. Checkout our batch reporting module.

Mark
RPT Software
http://www.rptsoftware.com

PS: If you want to create a web site for users to run their own reports and
dynamically create pdf
fils checkout our other program (Report Server) by far the more popular way
for report creation.