Loop through table and create filenames (1 Viewer)

Big Pat

Registered User.
Local time
Today, 22:39
Joined
Sep 29, 2004
Messages
555
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:

Code:
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)

'Warnings off
DoCmd.SetWarnings False

'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
DoCmd.SetWarnings True

End Sub

Thank you.
 

Big Pat

Registered User.
Local time
Today, 22:39
Joined
Sep 29, 2004
Messages
555
Hi namliam,
Thanks for the suggestion. But the report is already built in Access and has been through a period of careful formatting to make sure it’s right for all users and contains headings, charts, tables, text-boxes etc. So exporting to Excel is not really going to work for me.

I’m guessing it needs code that goes something like

Code:
For Specialty = 1 to 30
  ‘Run the queries
  ‘ Run the existing code up to the DoCmd.OpenReport line
  DoCmd.Save filename=”C:\MyFolder”&[Specialty],overwrite = Yes  
 [COLOR="red"]'The previous line is where I really need help. I don't know enough VBA to get this syntax right.[/COLOR]
Next Specialty
'[COLOR="Red"]And I don't know if For/Next is right.  I've seen code such as Do While Not EOF, but I don't know how to write it.[/COLOR]

‘Then restore the printer settings
‘Warnings back on 

End Sub

Is this kind of thing possible in VBA?
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:39
Joined
Aug 11, 2003
Messages
11,696
Code:
dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from tblspeciality")
do while not rs.eof
    'do stuff for each speciality
    rs.movenext
loop
rs.close
set rs = nothing
Docmd.save will not export your object, you may want to try docmd.outputto, see
http://msdn.microsoft.com/en-us/library/bb238050(office.12).aspx
It can also support to pdf, I read
 

Big Pat

Registered User.
Local time
Today, 22:39
Joined
Sep 29, 2004
Messages
555
Thank you. That certainly helps with the "Do while" part of things. I'm going to copy and paste that code into my "Help" file, as I can see that will be really useful for lots of things in future.

I have looked at that MSDN link and I think it will be useful too. It will take me some time to figure it out of course, but I'll probably learn more that way. I'll come back if (when!!) I need more help.

Thanks again.
 

bbwolff

Registered User.
Local time
Today, 23:39
Joined
Oct 1, 2013
Messages
116
i use this to SaveAsPdf

strusername = Environ("username")
strpath = "C:\Users\" & strusername & "\documents\Razpis " & Me.txtDate & ".pdf"
DoCmd.OutputTo acOutputReport, "Razpis", acFormatPDF, strpath, False

but you have to instal save as pdf possibility from Microsoft (at least for 2007 version)
you can do a loop and change the report name and file name to values from table
 

Users who are viewing this thread

Top Bottom