• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1545
  • Last Modified:

Export multiple reports automatically with VBA

In the attached database, I’m able to export a single report to a PDF file in a specified directory.  The data included in the report is dependent on what criteria is selected in my combo boxes.

When you select the zone ‘Atlantic’ in the combo box, you have a choice of two areas: ‘MD’ and ‘NJ’.  First select MD, then click the [Execute] and a report will be exported to the C drive.  

Instead of having to manually select each area individually to generate a report, I’m looking for a block of code that will generate a report automatically for each Area.  The report would be named after the Area, and it would be exported to a sub folder named after the Zone.

So a report with the Zone ‘Atlantic’ selected and the Area ‘MD’, the file would be exported to the following path: C:\Atlantic\MD.pdf

I really appreciate any feedback.  I realize this is a tough one.
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This is really more of a project than a request for help.

You can loop through the Areas associated with a Zone like this:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblMaster WHERE Zone='" & Me.cboZone.Column(0) & "'")

Do Until rst.EOF
  DoCmd.OpenReport "repDaily", , , "Area='" & rst("Area") & "'", acHidden
  DoCmd.OutputTo acOutputReport, "repDaily", acFormatPDF, Me.txtPath
  DoCmd.Close acReport, "repDaily"

As far as "outputting to a subfolder" ... you could alter Me.txtPath to point to a subfolder. You can check to see if the folder exists:

If Dir(Me.txtPath & "\" & Me.txtZone) = "" Then
  '/ the folder does not exist - use MkDir to create it
  '/ the folder does exist - do you need to empty it first?
End If

To create a new folder, if needed, use MkDir:

MkDir Me.txtPath & "\" & Me.txtZone

Then refer to that:

  DoCmd.OutputTo acOutputReport, "repDaily", acFormatPDF, Me.txtPath & "\" & Me.txtZone & "\" & rst("Area") & ".pdf"

Your user would have to be sure to select a Folder, and not enter a full document name/path.
KP_SoCalAuthor Commented:
I couldn't get this to work, especially the Loop, so I feel I need to break this up into multiple questions in separate posts.  I appreciate you taking the time in pointing me in the right direction.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now