Using SnapShot files to distribute reports

View as
a full page
article

Download
sample
database
A2K3 A97

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