Snapshot to pdf code

[Vavs]

I downloaded the zip file and looked at the code. I have code attached to a
form that creates the 70 reports I need based on a table. I want to
incorporate this code to take the file in snapshot and convert it to pdf
right away. If possible, I would like to save that file in a different
folder so that I don't have two files with the same name (different
extensions) hanging around. Can someone explain what I need to do to
incorporate the two sets of code?

 

[Stephen Lebans]

I do not understand your question. Are you referring to the ReportToPDF
solution? It creates a temporary Snapshot file which it deletes at the end
of the process. The end user does not have to convert their reports to
Snapshot format.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Vavs]

I have a program which basically loops through a table and produces custom
reports for a series of 70 managers. I am producing them in .snp format. I
would like to use your code to take the snp files and convert them to pdf so
that the managers can read them. I have a report with a series of sub
reports and the rtf output does not retain the original format.

I used your program on one of the files I had produced in test and it worked
very well. I guess I would rephrase my question to : How do I incorporate
the portion of the code which converts the snp to pdf? I am listing the code
I produced at the end of this message. I would like to think I can include
it somewhere in my loop.


Private Sub cmdOpen_Click()

Dim strDocName As String
Dim strWhere As String
Dim strWhereApp As String
Dim strSQL As String
Dim strApprover As String
Dim rs As Recordset
Dim db As Database
Dim strSnap As String


strDocName = "rptSoxJuly"
'strApprover = "Keith Gustely"
Dim qdf As QueryDef
Set qdf = DBEngine(0)(0).QueryDefs("rptSOXJuly")
Set db = CurrentDb()
Set rs = db.OpenRecordset("DistinctApprover", DB_OPEN_TABLE)
rs.MoveFirst

Do While Not rs.EOF

With qdf
strApprover = rs.Fields("AppName").Value
strWhereApp = "Where Approvers.AppName = " & "'" &
rs.Fields("AppName").Value & "'"

'strWhere = "Approvers.AppName =" & strWhereApp
strSQL = "SELECT AS400CSTCTR.*, Approvers.AdminApproverName,
Approvers.AppName FROM AS400CSTCTR INNER JOIN Approvers ON
AS400CSTCTR.dspSysCostCenter=Approvers.CSTCTR " & strWhereApp

'.SQL = "SELECT AS400CSTCTR.*, Approvers.AdminApproverName,
Approvers.AppName FROM AS400CSTCTR INNER JOIN Approvers ON
AS400CSTCTR.dspSysCostCenter=Approvers.CSTCTR WHERE ((Approvers.AppName) =
(strApprover))"
.SQL = strSQL
DoCmd.OpenReport strDocName, acViewPreview
DoCmd.OutputTo acOutputReport, , acFormatSNP, "c:\windows\SOXReports\" &
strApprover & ".snp"
strSnap = "c:\windows\SOXReports\" & strApprover & ".snp"
DoCmd.Close acReport, "rptSOXJuly", acSaveNo
End With
rs.MoveNext
Loop
End Sub

 

[Stephen Lebans]

You would simply insert the code to convert the SNapshot TO PDF at the end
of your inner loop(after you close the current report). Look at the code
behind the CommandButton on the sample COnvert Snapshot to PDF form. It's
that simple.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Vavs]

Stephan, I am not a programmer by trade, but I am trying. Here is how I put
the code together, but I get a message saying the program cannot uncompress
the snapshot file.

Private Sub cmdOpen_Click()

Dim strDocName As String
Dim strWhere As String
Dim strWhereApp As String
Dim strSQL As String
Dim strApprover As String
Dim rs As Recordset
Dim db As Database
Dim strSnap, strPDF As String


strDocName = "rptSoxJuly"
'strApprover = "Keith Gustely"
Dim qdf As QueryDef
Set qdf = DBEngine(0)(0).QueryDefs("rptSOXJuly")
Set db = CurrentDb()
Set rs = db.OpenRecordset("DistinctApprover", DB_OPEN_TABLE)
rs.MoveFirst

Do While Not rs.EOF

With qdf
strApprover = rs.Fields("AppName").Value
strWhereApp = "Where Approvers.AppName = " & "'" &
rs.Fields("AppName").Value & "'"

'strWhere = "Approvers.AppName =" & strWhereApp
strSQL = "SELECT AS400CSTCTR.*, Approvers.AdminApproverName,
Approvers.AppName FROM AS400CSTCTR INNER JOIN Approvers ON
AS400CSTCTR.dspSysCostCenter=Approvers.CSTCTR " & strWhereApp

'.SQL = "SELECT AS400CSTCTR.*, Approvers.AdminApproverName,
Approvers.AppName FROM AS400CSTCTR INNER JOIN Approvers ON
AS400CSTCTR.dspSysCostCenter=Approvers.CSTCTR WHERE ((Approvers.AppName) =
(strApprover))"
.SQL = strSQL
DoCmd.OpenReport strDocName, acViewPreview
DoCmd.OutputTo acOutputReport, , acFormatSNP, "c:\windows\SOXReports\SNP\" &
strApprover & ".snp"
strSnap = "c:\windows\SOXReports\SNP\" & strApprover & ".snp"
strPDF = "c:\windows\SOXReports\PDF\" & strApprover & ".pdf"
DoCmd.Close acReport, "rptSOXJuly", acSaveNo
'Private Sub cmdReportToPDF()
' Save the Report as a PDF document.
' The selected report is first exported to Snapshot format.
' The Snapshot file is then broken out into its
' component Enhanced Metafiles(EMF), one for each page of the report.
' Finally, the EMF's are converted to PDF pages within the master
' PDF document.

' The function call is:
'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

' RptName is the name of a report contained within this MDB
' SnapshotName is the name of an existing Snapshot file
' OutputPDFname is the name you select for the output PDF file
' ShowSaveFileDialog is a boolean param to specify whether or not to display
' the standard windows File Dialog window to select an exisiting Snapshot file
' CompressionLevel - not hooked up yet
' PasswordOwner - not hooked up yet
' PasswordOpen - not hooked up yet
' PasswordRestrictions - not hooked up yet
' PDFNoFontEmbedding - Do not Embed fonts in PDF. Set to 1 to stop the
' default process of embedding all fonts in the output PDF. If you are
' using ONLY - any of the standard Windows fonts
' using ONLY - any of the standard 14 Fonts natively supported by the PDF spec
'The 14 Standard Fonts
'All version of Adobe's Acrobat support 14 standard fonts. These fonts are
always available
'independent whether they're embedded or not.
'Family name PostScript name Style
'Courier Courier fsNone
'Courier Courier-Bold fsBold
'Courier Courier-Oblique fsItalic
'Courier Courier-BoldOblique fsBold + fsItalic
'Helvetica Helvetica fsNone
'Helvetica Helvetica-Bold fsBold
'Helvetica Helvetica-Oblique fsItalic
'Helvetica Helvetica-BoldOblique fsBold + fsItalic
'Times Times-Roman fsNone
'Times Times-Bold fsBold
'Times Times-Italic fsItalic
'Times Times-BoldItalic fsBold + fsItalic
'Symbol Symbol fsNone, other styles are emulated only
'ZapfDingbats ZapfDingbats fsNone, other styles are emulated only


' You must pass either RptName or SnapshotName or set the ShowSaveFileDialog
param to TRUE.
' Any file names you pass to this function must include the full path. If
you only include the
' filename for the output PDF then your document will be saved to your My
Documents folder.


Dim blRet As Boolean
' Call our convert function
' Please note the last param signals whether to perform
' font embedding or not. I have turned font embedding ON for this example.
blRet = ConvertReportToPDF(, "c:\windows\SOXReports\" & strApprover &
".snp", _
"c:\windows\SOXReports\" & strApprover & ".pdf", False, False, 0, "", "", 0,
0)
' To modify the above call to force the File Save Dialog to select the name
and path
' for the saved PDF file simply change the ShowSaveFileDialog param to TRUE.


End With
rs.MoveNext
Loop
End Sub

I would appreciate your insight.

 

[Vavs]

Stephan,

I found my error, I need to pass the snp file as a string and the pdf as a
parameter. If I left both as parameters it did not work for some reason. It
is now working famously. I am in your debt for helping me through this issue.

 

[Stephen Lebans]

Well Vavs, since you debugged this issue yourself... I now call you
"programmer".


--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Vavs]

There is an old saying that goes something like: "Even a blind dog can find a
bone now and again"

The code you wrote is great! I especially appreciate the comments, it helps
to follow the process.