I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.
But I run this report 30 times for 30 different parameters.
- The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke…
- I always name the PDF file according to this value.
- I always choose the same folder.
- I always overwrite the reports I produced last month (copies have been sent
elsewhere by then).
Given this scenario, how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?
If it’s of any use, my existing code is as follows:
Private Sub lstSpecialties_DblClick(Cancel As Integer)
‘At the moment I am running this code by double-clicking an entry in the Specialty listbox,
'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table
'(currently 30 but could be more in time)
'Make the tables needed for the cumulative queries
DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" ‘Each of these queries
DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" ‘uses the Specialty selected above
DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" ‘as a parameter to get
DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" ‘the right data for the report
'Produce the report
Dim defPrinter As String, NewPrinter As Printer
defPrinter = Application.Printer.DeviceName 'Get the default printer name
Set NewPrinter = Application.Printers("CutePDF Writer") 'Create a new printer object
Set Application.Printer = NewPrinter 'Set the default printer to the new printer
DoCmd.OpenReport "Spec 0 Main Report", acViewPrint 'Open the report in print view (which will PDF it)
Set NewPrinter = Application.Printers(defPrinter) 'Reset the printer back to the original default
'Warnings back on