Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to export reports

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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Linda,

    Could you please specify your version of Access as this makes a difference.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Access 2007 but will be upgrading to Access 2010 in near future. For now 2007.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\BEKDocs\Access\"
       
       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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was going to create a form and put the code in the on click event of a command button. Can I do this?

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Linda

    Just put the procedure name in the event procedure.

    Code:
    Private Sub ButtonName_Click()
       ExportReportsPDF
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your help. Will try this on Monday.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:\CORPDATA17\Admin\Linda\DelawareReportPDFFolder\ "

    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

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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

    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:\BEKDocs\Access\"
       
       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!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    was that reply helpful?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •