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

 

=========
ANSWER
———
Access 2010 option of opening report and outputting to PDF using the Access engine, not Adobe Acrobat Printer: see EXAMPLES

———
Other options:
http://www.granite.ab.ca/access/pdffiles.htm

 

=========
EXAMPLES
———
Private Sub SendReports_Click()

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

'1010_Report1
'Export to Shared drive

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

MyFilter = ""
MyPath = "S:SystemsReportsReport1"
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:PRODDATAReportsAllRecip" & MyFilename

'1020_Report2
'Export to Share drive

MyFilter = ""
MyPath = "S:SystemsReportsReport2"
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:PRODDATAReportsAllRecip” & MyFilename

'Clean up!

Set fs = Nothing

Me.CurrentStatus = "Sending Reports: Completed." & vbCrLf & "Placed at: " & vbCrLf & "Q:PRODDATAReportsAllRecip" & vbCrLf & "S:SystemsReports" & vbCrLf & vbCrLf & Me.CurrentStatus
Me.Repaint

End Sub

 

———
EXAMPLE WITH LOOPING:

Private Sub SendReports_Click()

    DoCmd.Hourglass True
    DoCmd.SetWarnings False
   
    Set rs = CurrentDb.OpenRecordset(“DistrictList”, dbOpenDynaset, dbReadOnly)
    rs.MoveFirst
   
    MyReport = “NameInAccess”
    PDFName = “FileName.pdf”
    MyPath = “S:SubDir1SubDir2SubDir3”
   
   
    Me.CurrentStatus = _
        “Sending Reports…” & vbCrLf & _
        vbCrLf & _
        vbCrLf & _
        “=========” & vbCrLf & _
        Me.CurrentStatus
    Me.Repaint
   
    MyCounter = 1
   
    While Not rs.EOF
        MyFilter = “[DISTRICT] = ” & rs!DISTRICT
        MyPath_inLoop = MyPath & “D” & Format(rs!DISTRICT, “000”)
        MyFilename = MyPath_inLoop & PDFName
       
        If Len(Dir(MyPath_inLoop, vbDirectory)) = 0 Then
            MkDir MyPath_inLoop
        End If
       
        Me.CurrentStatus = _
            MyCounter & ” – Sending to District ” & Format(rs!DISTRICT, “000”) & vbCrLf & _
            Me.CurrentStatus
        Me.Repaint
       
        DoCmd.OpenReport MyReport, acViewPreview, , MyFilter
        DoCmd.OutputTo acOutputReport, “”, acFormatPDF, MyFilename, False
        DoCmd.Close acReport, MyReport
       
        MyCounter = MyCounter + 1
        MyPath_inLoop = “”
        rs.MoveNext
    Wend
   
    DoCmd.Hourglass False
    DoCmd.SetWarnings True

End Sub

 

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
PDF
VBA
Report
Export
PDF Convert
Convert to PDF

 

=========
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