Access: Export report to PDF using VBA

=========
QUESTION
---------
Using VBA, how do I export a report to PDF in Microsoft Access?

=========
ANSWER
---------
See EXAMPLES

=========
EXAMPLES
---------
Private Sub SendReports_Click()

Me.CurrentStatus = "Sending Reports..." & vbCrLf & vbCrLf & vbCrLf & "=========" & vbCrLf & Me.CurrentStatus
Me.Repaint

'=========
'1010_Report1
'---------
'Export toShared drive

Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String

MyFilter = ""
MyPath = "S:\Systems\Reports\Report1\"
MyFilename = "Report1.pdf"

'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
DoCmd.OpenReport "1010_Report1", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False

'Let's close our previewed report
DoCmd.Close acReport, "1010_Report1"


'---------
'Copy to Q drive as well

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

fs.CopyFile MyPath & MyFilename, "Q:\PROD\DATA\Reports\AllRecip\" & MyFilename

'=========
'1020_Report2
'---------
'Export to Share drive

MyFilter = ""
MyPath = "S:\Systems\Reports\Report2\"
MyFilename = "Report2.pdf"

DoCmd.OpenReport "1020_Report2", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
DoCmd.Close acReport, "1020_Report2"

'---------
'Copy to Q drive as well

fs.CopyFile MyPath & MyFilename, "Q:\PROD\DATA\Reports\AllRecip\" & MyFilename

'---------
'Clean up!

Set fs = Nothing

Me.CurrentStatus = "Sending Reports: Completed." & vbCrLf & "Placed at: " & vbCrLf & "Q:\PROD\DATA\Reports\AllRecip\" & vbCrLf & "S:\Systems\Reports\" & vbCrLf & vbCrLf & Me.CurrentStatus
Me.Repaint

End Sub

---------
EXAMPLE WITH LOOPING:

Private Sub Command1_Click()

Dim rs As Recordset
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
Dim MyReport As String
Dim PDFName As String

DoCmd.Hourglass True
DoCmd.SetWarnings False

'---------
'201_Active_Users_ByDist

Set rs = CurrentDb.OpenRecordset("Districts", dbOpenDynaset, dbReadOnly)
rs.MoveFirst

MyReport = "201_Active_Users_ByDist"
PDFName = "\Report1.pdf"
MyPath = "N:\Reports\"

While Not rs.EOF

MyFilter = "[DISTRICT] = " & rs!DISTRICT

MyFilename = "D" & Format(rs!DISTRICT, "000") & PDFName

Me.dfStatus = "Sending to District " & Format(rs!DISTRICT, "000")
Me.Repaint

DoCmd.OpenReport MyReport, acViewPreview, , MyFilter

DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
DoCmd.Close acReport, MyReport

rs.MoveNext
Wend

=========
APPLIES TO / KEY WORDS
---------
Microsoft Access
PDF
VBA
Report
Export

=========
REF
---------
http://social.msdn.microsoft.com/Forums/en/isvvba/thread/bf5dc54a-9c39-42e7-a175-2984825a60be

---
http://www.anysitesupport.com/access-export-report-to-pdf-using-vba/
http://anySiteHosting.com

  • 12 Users Found This Useful
Was this answer helpful?