Output To PDF

D

doodle

A2k

short version:
i need to know how to save an access report as a PDF file without the
user being prompted for a filename or location.


long version:
i need to create a PDF report for all records that have a status of
'Clsoed' in my db. This will be ongoing and will run every night. To
get up to speed, I have about 10k reports to create. right now this
works for creating snapshot reports, but i can't get it to work for
pdf files. I created a recordset with my unique values for each
report, then i loop thru the recordset, dynamically changing the sql
for the reports recordsource, output the report and repeat.

here is my code:

Private Sub cmdLoop_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOldSQL As String
Dim strQry As String
Dim strRpt As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qrySys_CreatePDF") 'Hazard/
Engine combinations

Do While Not rs.EOF

strQry = "qrySys_CreatePDFSource"

strSQL = "SELECT h.[Haz_Trk_No] & [Engine_Mod] AS HTRModel, " & _
" h.[Haz_Trk_No] & [Engine_Mod] AS HTRModelTwo," & _
" h.Haz_Trk_No , h.Date_Open, h.SAR, h.Title, h.Assy_Desc,
h.Part_Desc, h.Safety_Own," & _
" h.RootCause, h.WorklistDate, h.Hazdescpt, h.BriefHazDesc,
ms.Engine_Mod, ms.Status," & _
" ms.Stat_Date, ms.Statusinfo, ms.TaskOwnr, ms.PlanContPlan,
ms.RevContPlan," & _
" ms.ActlContPlan, ms.RiskAssess, ms.[ECP/TCTO Number]," & _
" ms.[TCTO/PPC Number], ms.QTR_status_AI_link, ms.pop, ms.Q_comp,
ms.P_comp_date, ms.P_actions," & _
" ms.EPD, ms.ICA, ms.FCA, ms.FundingSource, ms.ActualNRIFSD,
ms.ActualERLOA, ms.EventsSinceICA," & _
" al.[Statement No], al.Engineer, ms.RiskAssess" & _
" FROM (tbHAZARD AS h INNER JOIN tbMainSTATUS AS ms ON
h.Haz_Trk_No = ms.Haz_Trk_No) " & _
" LEFT JOIN Assessment_Log AS al ON ms.RiskAssess = al.[Statement
No]" & _
" WHERE (h.[Haz_Trk_No] & [Engine_Mod]) = " & "'" & rs!HTRModel &
"'" & _
" ORDER BY h.[Haz_Trk_No] & [Engine_Mod] DESC , h.[Haz_Trk_No] &
[Engine_Mod] DESC ," & _
" ms.Stat_Date DESC;"


strOldSQL = ChangeSQL(strQry, strSQL)

DoCmd.OutputTo acOutputReport, "rptHTRDetail_PDF", "Snapshot
Format", "C:\" & rs!HTRModel & ".snp"

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing


End Sub
 
A

Arvin Meyer [MVP]

You can save money by using Stephen Lebans code:

http://www.lebans.com/reporttopdf.htm

or you can use Steve Arbaugh's PDF library:

http://www.groupacg.com/

and a pdf maker like Win2PDF:

http://www.win2pdf.com

I've use both and prefer the second for speed and functionality, and the
first for simplicity and ease of operation. I supply file names by using a
combination of data like VendorCode and a generated number, but you can also
use a timestamp as a file name because it won't ever be repeated unless you
reset the time on the computer.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

doodle said:
A2k

short version:
i need to know how to save an access report as a PDF file without the
user being prompted for a filename or location.


long version:
i need to create a PDF report for all records that have a status of
'Clsoed' in my db. This will be ongoing and will run every night. To
get up to speed, I have about 10k reports to create. right now this
works for creating snapshot reports, but i can't get it to work for
pdf files. I created a recordset with my unique values for each
report, then i loop thru the recordset, dynamically changing the sql
for the reports recordsource, output the report and repeat.

here is my code:

Private Sub cmdLoop_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOldSQL As String
Dim strQry As String
Dim strRpt As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qrySys_CreatePDF") 'Hazard/
Engine combinations

Do While Not rs.EOF

strQry = "qrySys_CreatePDFSource"

strSQL = "SELECT h.[Haz_Trk_No] & [Engine_Mod] AS HTRModel, " & _
" h.[Haz_Trk_No] & [Engine_Mod] AS HTRModelTwo," & _
" h.Haz_Trk_No , h.Date_Open, h.SAR, h.Title, h.Assy_Desc,
h.Part_Desc, h.Safety_Own," & _
" h.RootCause, h.WorklistDate, h.Hazdescpt, h.BriefHazDesc,
ms.Engine_Mod, ms.Status," & _
" ms.Stat_Date, ms.Statusinfo, ms.TaskOwnr, ms.PlanContPlan,
ms.RevContPlan," & _
" ms.ActlContPlan, ms.RiskAssess, ms.[ECP/TCTO Number]," & _
" ms.[TCTO/PPC Number], ms.QTR_status_AI_link, ms.pop, ms.Q_comp,
ms.P_comp_date, ms.P_actions," & _
" ms.EPD, ms.ICA, ms.FCA, ms.FundingSource, ms.ActualNRIFSD,
ms.ActualERLOA, ms.EventsSinceICA," & _
" al.[Statement No], al.Engineer, ms.RiskAssess" & _
" FROM (tbHAZARD AS h INNER JOIN tbMainSTATUS AS ms ON
h.Haz_Trk_No = ms.Haz_Trk_No) " & _
" LEFT JOIN Assessment_Log AS al ON ms.RiskAssess = al.[Statement
No]" & _
" WHERE (h.[Haz_Trk_No] & [Engine_Mod]) = " & "'" & rs!HTRModel &
"'" & _
" ORDER BY h.[Haz_Trk_No] & [Engine_Mod] DESC , h.[Haz_Trk_No] &
[Engine_Mod] DESC ," & _
" ms.Stat_Date DESC;"


strOldSQL = ChangeSQL(strQry, strSQL)

DoCmd.OutputTo acOutputReport, "rptHTRDetail_PDF", "Snapshot
Format", "C:\" & rs!HTRModel & ".snp"

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing


End Sub
Click to expand...
 
P

pietlinden

1. just a comment...
you can do

dim rs as recordset
set rs = db.Querydefs("MyQuery").OpenRecordset


2. Stephen Lebans has written code to output reports to PDF. I think
it's in the Report Utilities at www.lebans.com
 
D

doodle

pietlin:

lebans' code requires user interaction (providing a filename). i am
looking for something more automated.
 
D

doodle

arvin,

thanks for your response. i have looked at those sites before. I have
the full version of acrobat writer 7.0. spending additional $ is not
an option for this project. I need to figure out how to get writer to
work.

a
 
T

Tony Toews [MVP]

doodle said:
lebans' code requires user interaction (providing a filename). i am
looking for something more automated.
Click to expand...

You can pass the file name in code. I've done that lots.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
M

Mike Mueller

You can create a profile in the acrobat printer which will not ask for the
location/filename, and after that change your report to print utilizing that
profile.
 
D

doodle

hi tony. can you tell me the syntax for passing the report name? i am
using the code from the mvp's site. i can't post it because of
copyright, but it had a line to change the filename and it isn't
working. another developer told me that he used the same code many
times and that adobe must have changed something in 7.0. Have you had
any luck with that version? Can you send me some syntax?

a
 
P

Paul Shapiro

The only way I found to specify the Acrobat file name is setting a registry
key. The key varies depending on the Acrobat version. In all cases the key
is in the CurrentUser part of the registry.

Here is simplified code I used for Acrobat versions 5, 6 and 7. Note that
for this to work
the user MUST have set the printer defaults to
a) not prompt for filename (no support for setting this via registry in
Acrobat 6)
b) desired location for output files, or leave blank for source folder.
You can specify the folder (if I remember correctly) in the registry key
setting.

I have nicer code now, which includes version 8, automates some of the
checking, and supports both Word documents and Access reports. Send me an
email by removing the "hideme" if you'd like a copy.

'Setup for acrobat printing
Select Case lngAcrobatVersion
Case 5
'Set the printer
mobjWord.ActivePrinter = "Acrobat PDFWriter"

'Set the registry value telling Acrobat the file name
fSuccess = pjsRegistryHKCUWrite( _
strSubkey:="Software\Adobe\Acrobat PDFWriter", _
strValueName:="PDFFileName", _
strValueValue:=strAcrobatFileNameWithPath _
)
Case 6
'Set the printer
mobjWord.ActivePrinter = "Adobe PDF"

'Set the registry value telling Acrobat the file name
fSuccess = pjsRegistryHKCUWrite( _
strSubkey:="Software\Adobe\Acrobat\PDFMaker\6.0\Global\Settings", _
strValueName:="PDFFileName", _
strValueValue:=strAcrobatFileNameWithPath _
)
Case 7
'Set the printer
mobjWord.ActivePrinter = "Adobe PDF"

'Set the registry value telling Acrobat the file name, _
as per the Distiller API Reference. _
This key will automatically be deleted once the next pdf file is
created _
by printing to Adobe PDF printer from the specified application. _
NOTE: This only works if Adobe PDF printer preferences specify
prompting _
for the file name!
'The registry key name must be the full application path and name.
strWordEXEFullPathName = mobjWord.Path & "\" & "WINWORD.EXE"
fSuccess = pjsRegistryHKCUWrite( _
strSubkey:="Software\Adobe\Acrobat Distiller\PrinterJobControl", _
strValueName:=strWordEXEFullPathName, _
strValueValue:=strAcrobatFileNameWithPath _
)
Paul Shapiro
 
A

Arvin Meyer [MVP]

Look for this line of code. In the sample it is in the command button that
creates the PDF:

blRet = ConvertReportToPDF(Me.lstRptName, vbNullString, _
Me.lstRptName.Value & ".pdf", False, True, 0, "", "", 0, 0)

This is the report's name: Me.lstRptName.Value & ".pdf"

Replace: Me.lstRptName.Value

with your routine to create a name, like for instance:

"Report for " & Format(Date, "mmddyy") & ".pdf"

which will create a pdf with the name:

Report for 080607.pdf
 
T

Tony Toews [MVP]

doodle said:
hi tony. can you tell me the syntax for passing the report name? i am
using the code from the mvp's site. i can't post it because of
copyright, but it had a line to change the filename and it isn't
working.
Click to expand...

Here is exact lines of code from the working code in the clients
database.

blnReturnCode = ConvertReportToPDF(stDocName,
vbNullString, FilePathName, False, _
Me.chkViewPDFFiles And Me.frmFilesToCreate = 1)

Following are the lines of code from the subroutine.

Public Function ConvertReportToPDF( _
Optional RptName As String = "", _
Optional SnapshotName As String = "", _
Optional OutputPDFname As String = "", _
Optional ShowSaveFileDialog As Boolean = False, _
Optional StartPDFViewer As Boolean = True, _
Optional CompressionLevel As Long = 0, _
Optional PasswordOwner As String = "", _
Optional PasswordOpen As String = "", _
Optional PasswordRestrictions As Long = 0, _
Optional PDFNoFontEmbedding As Long = 0 _
) As Boolean
another developer told me that he used the same code many
times and that adobe must have changed something in 7.0.
Click to expand...

Adobe 7.0? I'm discussing the Lebans solution.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
populate listbox form database table 0
Opening a recordset in a function 3
how to code a button to export results of stored procedure to excel file 0
migrating code from Access 2000 to 2003 and 2007 5
dlookup and dao.recordset 4
Export specific fields from access to Excel 0
hanging 12

Top