Using SnapShot files to distribute reports

This article from Microsoft Office Online reviews the use of the Snapshot file format to distribute reports.

I've used snapshot in my organization for a couple of years now and have found them very usable. They provide all of the benefits normally associated with PDF files, and can be easily generated from Access without any additional components. Compared with saving to Word or RTF they have the advantage of preserving all your report formatting.

The article covers the basics of saving a snapshot file manually from a print preview window, but it doesn't cover automating the production of snapshot files. What makes this task harder is that the the snapshot format is not covered in the help or the list of allowed constants for the relevant commands. Despite this, it's really easy once you know how. And in this post I'll tell you how.

Here's basic example code to run a report and save it as a snapshot file to a folder in the user's temp folder.

Public Sub RunFullReportToSnapshotFile()

 Dim strOutputFileName As String
 
 strOutputFileName = GetTempFolderName & "\" & "SalesReport.snp"
 
 Kill strOutputFileName ' delete report file if it exists
 
 DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Sales by Category", _
 OutputFormat:="Snapshot Format", OutputFile:=strOutputFileName
 
 MsgBox "Report saved as " & strOutputFileName
 
End Sub

The code above (and the remaining examples) use a couple of simple library routines which are not core to the issue at hand so you can use or not)- here's that code (requires a reference to Microssoft Scripting Runtime):

Public Function GetTempFolderName() As String
 ' use a FileSystemObject to retruns the name of the user's temp folder
 ' in this case FSO is early bound- it's common to late bind with this library but
 ' this works well and provides compil time checking
 ' requires a reference to Microssoft Scripting Runtime
 ' path will not include a slash
 ' path will be expressed using short (8.3) names for folders
 
 Dim fso As Scripting.FileSystemObject ' an insteance of the FileSystemObject
 
 Set fso = New FileSystemObject

 GetTempFolderName = fso.GetSpecialFolder(TemporaryFolder).Path
 
 Set fso = Nothing

End Function

Public Sub DeleteFileIfExists(strFileSpec As String)

 Dim fso As Scripting.FileSystemObject
 
 Set fso = New Scripting.FileSystemObject
 
 If fso.FileExists(strFileSpec) Then
 fso.DeleteFile (strFileSpec)
 Debug.Print strFileSpec; " deleted"
 Else
 Debug.Print strFileSpec; " doesn't exist"
 End If
 
 Set fso = Nothing
End Sub

The next example saves each category's report as a separate file. To do this you ned to open the report with the appropriate filter before using Docmd.OutputTo to make the snapshot file.

Public Sub RunReportsByCategoryToSnapshotFile()

 Dim rsCategories As DAO.Recordset
 Dim lngCategoryID As Long
 Dim strCategoryName As String
 Dim strOutputFileName As String
 Dim strFileList As String
 
 Const strcReportName As String = "Sales by Category"
 
 Set rsCategories = CurrentDb.OpenRecordset(Name:="Categories", Type:=dbOpenForwardOnly, Options:=dbReadOnly)
 
 With rsCategories
 
 Do Until .EOF
 
 lngCategoryID = !CategoryID
 strCategoryName = !CategoryName
 strCategoryName = Replace(strCategoryName, "/", ",")
 
 strOutputFileName = GetTempFolderName & "\" & "SalesReport_" & strCategoryName & ".snp"
 
 DoCmd.OpenReport ReportName:=strcReportName, View:=acViewPreview, _
 WhereCondition:="CategoryID=" & CStr(lngCategoryID), WindowMode:=acHidden
 
 
 DeleteFileIfExists strOutputFileName
 
 DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:=strcReportName, _
 OutputFormat:="Snapshot Format", OutputFile:=strOutputFileName
 
 DoCmd.Close ObjectType:=acReport, ObjectName:=strcReportName
 
 strFileList = strFileList & vbCrLf & strOutputFileName
 
 .MoveNext
 Loop
 End With
 
 rsCategories.Close
 Set rsCategories = Nothing
 
 MsgBox "Reports saved as: " & strFileList

End Sub

Finally here's one that runs each category separately but instead of saving it to a folder the reports are emailed.

Public Sub RunReportsByCategoryToSnapshotEmail()

 Dim rsCategories As DAO.Recordset
 Dim lngCategoryID As Long
 Dim strCategoryName As String
 Dim intEmailCount As Integer
 
 Const strcReportName As String = "Sales by Category"
 
 Set rsCategories = CurrentDb.OpenRecordset(Name:="Categories", Type:=dbOpenForwardOnly, Options:=dbReadOnly)
 
 With rsCategories
 
 Do Until .EOF
 
 lngCategoryID = !CategoryID
 strCategoryName = !CategoryName
 
 DoCmd.OpenReport ReportName:=strcReportName, View:=acViewPreview, _
 WhereCondition:="CategoryID=" & CStr(lngCategoryID), WindowMode:=acHidden
 
 DoCmd.SendObject ObjectType:=acSendReport, ObjectName:=strcReportName, _
 OutputFormat:="Snapshot Format", To:="Sales Manager ", _
 Subject:="Weekly sales report for " & strCategoryName, EditMessage:=False
 
 DoCmd.Close ObjectType:=acReport, ObjectName:=strcReportName
 
 intEmailCount = intEmailCount + 1
 
 .MoveNext
 Loop
 
 End With
 
 rsCategories.Close
 Set rsCategories = Nothing
 
 MsgBox CStr(intEmailCount) & "reports sent by e-mail"

End Sub
Comments