Macro to Export Separate Report Pages to PDF File

[jcbhydro]

Hi Folks,

I have a 50 page Report from which I need to export the pages separately to individual pdf files for multiple recipients.
I have built a macro which works to export the whole report to a pdf file, but I cannot fathom how to make the macro select each page in turn from the report.

Any suggestions would be gratefully received.

jcbhydro

 

[Old Man Devin]

As far as I know, the only way to do with is have a macro that opens the report 50 times but with different criteria so that in effect, you are opening each page one by one. Every time you open these one-page reports you export them to PDF, then close them, change the record source of the report so that only details from the next page appear, reopen, and go on until you'll done it enough times.

This is of course requires there to be a way to distinguish between pages in the record source. For example, if each page concerns a different person, you might add WHERE PersonID = Whatever as a condition to the record source, changing the value of 'Whatever' on each iteration.

Other than that, I have not come across a way to open specific pages of a report.

 

[jcbhydro]

Many thanks for the comments on the above.

I recognized that a page identifier is required and I have been using the filter expression as where [Groups].[Group ID] = "A1". Groups is the table and the Group ID is unique for each page. The Groups are all identified by A1,A2, B1,B2.B3, w1,w2, etc..

I had hoped that re-opening the Report after the first export and quoting a new identifier would achieve sequential export of the second and subssequent pages. Unfortunately, because the Report has not been closed and re-opened with the 2nd identifier, I simply get 2 exports of the first selection.
I cannot find an action to close the report. I did try to stop the micro after each export, but that produced the same result.

Is there a 'close report' or equivalent hidden away among the controls? I would be grateful for further assistance.

Regards,

jcbhydro

 

[Old Man Devin]

Try this line:

DoCmd.Close acReport, "ReportTitle"

Should just close the report entirely.

 

[jcbhydro]

Thanks again,
Hopefully that should do the trick, but I can't find a suitable action in the action catalogue which will accept such a line of code.

Are you perhaps assuming that the routine I have so far written is in VBA, something in which I have virtually no experience, but which I am happy to try.

jcbhydro

 

[Old Man Devin]

Ah yes I was assuming you were using VBA!
So are you changing the filters manually then? The whole process could be automated in VBA, but it's not a beginner-level task.

So is it the case that you just need to close the report in between manual exports?

 

[jcbhydro]

I was reluctantly prepared to build an action based macro, repeating the actions for each ;filter' of [Group ID] in turn. This would have been a laborious process, but only necessary once. This is the process which required the Report Print Preview to be closed after each export.
I have only limited experience of VBA, but would be prepared to have a go. If I convert the current macro to VBA would the structure of the coding give me a clue as to how to proceed?

Your assistance so far is much appreciated,

jcbhydro

 

[Old Man Devin]

It might be easier to just try and close the report as part of the macro - I believe 'Close' is one of the actions you can select, then you can specific in the 'Object Name' box that it is your Report that you wish to close.

In VBA you can change the filters by editing the 'filter string', but its a bit tricky as it uses SQL syntax. It's all stuff that is possible to look up, but it will be frustrating to sort out I think, so try what you're familiar with first if you only ever have to do this once.

 

[jcbhydro]

Eureka,

I eventually found a 'Close' Command under the RunMacrCommand action and that does the trick. It closes the current selection and facilitates a new ID selection.

It will be a painful typing job to incorporate all 52 selections, but at least the method is now clear.

Is there a limit to the number of sequential macro actions? If there is, I suppose it should be possible to nest macros.

I am very grateful for your help in achieving a result.

jcbhydro

 

[Old Man Devin]

Ah very good. Yes the iteration over the different sections is probably the bit that would have been far faster in code, but given that you'd have to account for the time needed to learn and use the code in the first place, I suspect the macro method will still be faster!

I've never encounter a limit to macros, but then again I've never put more than 10 or so actions into one. You can always make the final action to just be call another macro which immediately resumes the process if there is a limit. Good luck with building it!

 

[jcbhydro]

I actually built 2 macros, each with about 27 /open, export & close' routines, the 2nd macro simply being an edited copy of the first. So that confirms that Access can handle about 80 actions in a single macro.
As you suggested, i could have nested them but 2 separate macros are no problem.
Thanks to your suggestions I can confidently tackle further macro building projects in the future.

Regards,

jcbhydro

 

[Old Man Devin]

Sounds awesome, glad it all came together!