macro to export reports

Topic

New Reply

I need a macro to export 14 reports in PDF format to a location on C drive or network drive. Maybe prompted for location.

Reply | Quote

Replies

Linda,

Could you please specify your version of Access as this makes a difference. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Access 2007 but will be upgrading to Access 2010 in near future. For now 2007.

Reply | Quote

Linda,

I did this in 2010 but should also work in 2007.

Code:

Sub ExportReportsPDF()

   Dim zDestFolder         As String
   Dim zReportName(1 To 2) As String
   Dim iCntr               As Integer
   
   zReportName(1) = "rptBookInventoryInsert"
   zReportName(2) = "rptInventoryReport"
   
   zDestFolder = "G:BEKDocsAccess"
   
   For iCntr = 1 To UBound(zReportName)
   
      DoCmd.OutputTo acOutputReport, zReportName(iCntr), acFormatPDF, _
                     zDestFolder & zReportName(iCntr) & ".pdf"
      
   Next iCntr

End Sub

Of course you’ll have to change the array size and initialize each element of the array with the desired report name. You’ll also have to change the zDestFolder path and make sure you keep the trailing . HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I was going to create a form and put the code in the on click event of a command button. Can I do this?

Reply | Quote

Linda,

Yes you can just copy the code w/o the Sub/End Sub lines or include the code in a module {changing the 1st line to Public Sub …} and then call it from the click event, this is what I would recommend as you can then call it from elsewhere if needed. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I put the code in a module and when you say call it from the click event, what does that look like? Sorry, I am not that good at VBA.

Reply | Quote

Linda

Just put the procedure name in the event procedure.

Code:

Private Sub ButtonName_Click()
   ExportReportsPDF
End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you for your help. Will try this on Monday.

Reply | Quote

I am getting an error 2501 The outputTo action was cancelled.

Here is my module that I created. I created a form with a command button that calls the module.

Public Sub ExportReportsPDF()

Dim zDestFolder As String
Dim zReportName(1 To 13) As String
Dim iCntr As Integer

zReportName(1) = “01_Total All Members ReportAll”
zReportName(2) = “01_Total All Members ReportExcludesPre65”
zReportName(3) = “01_Total All Members ReportExcludesPre65-NONOPEB”
zReportName(4) = “01_Total All Members ReportExcludesPre65-OPEB”
zReportName(5) = “01_Total All Members ReportNON-OPEB”
zReportName(6) = “01_Total All Members ReportOPEB”
zReportName(7) = “01_Total All Members ReportPre65”
zReportName(8) = “01_Total All Members ReportPre65andNONOPB”
zReportName(9) = “01_Total All Members ReportPre65andOPEB”
zReportName(10) = “03RPt_CntbyProduct_BlueCare2”
zReportName(11) = “03RPt_CntbyProduct_CompPPO2”
zReportName(12) = “03RPt_FirstStBasicandCDHGold”
zReportName(13) = “03RPt_Medicfill and POS”

zDestFolder = ” V:CORPDATA17AdminLindaDelawareReportPDFFolder”

For iCntr = 1 To UBound(zReportName)

DoCmd.OutputTo acOutputReport, zReportName(iCntr), acFormatPDF, _
zDestFolder & zReportName(iCntr) & “.pdf”

Next iCntr

End Sub

I named the module ExportPDF

Reply | Quote

Linda,

On which report does it throw the error?
It could be the reports with the spaces in the names.
Try changing the For statement to start at 10 if that works it is the spaces in the report names.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Linda,

Here’s a revised version of my original code with some error trapping included. This should let reports w/o problems be saved while flagging those with errors so you can track down some of the problems. HTH :cheers:

Code:

Sub ExportReportsPDF()

   Dim zDestFolder         As String
   Dim zReportName(1 To 2) As String
   Dim iCntr               As Integer
   
   zReportName(1) = "rptBookInventory Insert"
   zReportName(2) = "rptInventoryReport"
   
   zDestFolder = "G:BEKDocsAccess"
   
   On Error GoTo PDFErrorTrap   'Set Error Trap
   
   For iCntr = 1 To UBound(zReportName)
   
      DoCmd.OutputTo acOutputReport, zReportName(iCntr), acFormatPDF, _
                     zDestFolder & zReportName(iCntr) & ".pdf"
      
   Next iCntr
   
GoTo ExitTag          'Branch around error trap

PDFErrorTrap:         'Error Trap Code

   Select Case Err.Number
         Case 2501
             MsgBox "Report: " & zReportName(iCntr) & " caused an error.", _
                    vbOKOnly + vbCritical, "Report Error:"
            Resume Next
         Case 2059
             MsgBox "Access could not find the report:" & vbCrLf & _
                    zReportName(iCntr), vbOKOnly + vbCritical, _
                    "Repport Not Found Error:"
             Resume Next
         Case Else
             MsgBox "Error " & Err.Number & ", " & Err.Description & vbCrLf & vbCrLf & _
                    "Procedure: ExportReportsPDF " & vbCrLf & _
                    "Report   : " & zReportName(iCntr)
   End Select
    
ExitTag:
   
   On Error GoTo 0    'Cancel Error Trap

End Sub

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Error 2501 suggests to me that the report has nothing to report, so i would change the test in RetiredGeeks code to test if it is 2501 to just resume next without the message box.

Reply | Quote

was that reply helpful?

Reply | Quote