Conver Report to pdf programatically...?

[rfuscjr via AccessMonster.com]

I am going to be greedy here too. We have Adobe Professional 6. We set up an
adobe print drive. I can open any access report and print to the adobe driver
and it converts the report format to pdf and outputs the file to the specific
file location specified during the adobe driver set up.

It works great; assuming you always want to write the output to the same file
location.

We want to be able to send the report output put to a variety of locations so
that puts the kabosh on the adobe print driver option. What we really want
would be something that emulated the acOutputRFT or acOutputSNP vba features
that can be executed via code. We are not looking for those options that
force you to open the report via an adobe dropdown installed off the Access
menu. Again, it needs to be executable in some way. Crystal reports has
this feature; I can not believe Access does not.

Ideas?

 

[John Spencer]

Access 97 to Access 2003

See Stephen Lebans

A2000ReportToPDF is an Access 2000 database containing a function to
convert Reports and Snapshot files to PDF documents. No PDF Printer
driver is required. This archive contains new versions of the StrStorage
and DynaPDF libraries. Please make sure you delete your old versions,
especially if you copied them into your Windows\System32 folder.

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

Access 2007
Add download from Microsoft
http://www.microsoft.com/downloads/...11-3e7e-4ae6-b059-a2e79ed87041&DisplayLang=en

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[rfuscjr via AccessMonster.com]

John, Thanks!!!
I am no super techie guy but was able to open his db insert my report name
and get the thing to work BUT.....when I stick the function call into my code
(behind my create report button) somethng VERY strange happens. I have code
that runs thru a file of ids: tblAllProviderIds and moves the first id in
that file into a second file: tblReportProviderId. This second table is
linked in the Report's control source query pull data from my:
MasterReportDataTable for just that one particular id. It then opens the
Access Report, Refreshes it to the share as an .RTF (with the id as part of
the file name) and continues to loop in this fashion until a report has been
generated for all ids...hope this makes sense.

Anyhoo,
I stuck the function at the appropriate spot to create a pdf file and it
failed at that point with a variable not defined error on: blRet. I forgot
my DIM blRet As Boolean statement!!!!

I added it and ran again and now it fails sooner with a type mismatch on:

Set rstTemp = db.OpenRecordset("MasterReportId", dbOpenTable)


Note that rstTemp was indeed earlier set as: Dim rstTemp As Recordset

I see no relation between the two yet when I remove Dim blRet As Boolean it
gets past the Set rstTemp statement. As soon as I put the Dim blRet in, I
get the type mismatch.

Any ideas?

Some sample code is provided so get the idea...


Dim db As Database
Set db = CurrentDb
Dim strSqlStmnt As String
Dim qdf As QueryDef
Dim stDocName As String
Dim xcelapp As Object
Dim rstCCs As Recordset
Dim RptName As String
Dim rstTemp As Recordset
Set xcelapp = CreateObject("Excel.Application")
Set qdf = db.CreateQueryDef("")
Dim passNbr As Integer
'Dim blRet As Boolean
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryLOAD_ActiveProviderIds", acViewNormal
Set rstTemp = db.OpenRecordset("tblReportProviderId", dbOpenTable)
Set rstCCs = db.OpenRecordset("tblAllProviderIds", dbOpenTable)

With rstCCs
.MoveFirst
Do Until rstCCs.EOF
RptName = "X:\LVPG\LVPG Website Files\" & rstCCs!LVPGID &
"_wRVU_Capacity_Sheet"
& "_wRVU_Capacity_Sheet"

strSqlStmnt = "INSERT INTO tblReportProviderId ( LVPGID )SELECT Min
(tblAllProviderIds.LVPGID) AS MinOfLVPGID FROM tblAllProviderIds"
qdf.SQL = strSqlStmnt
qdf.ODBCTimeout = 0
qdf.Execute


blRet = ConvertReportToPDF("RptMaster", vbNullString, _
RptName & ".pdf", False, True, 150, "", "", 0, 0, 0)