Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-22-2014, 01:04 AM   #1
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 555
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Loop through table and create filenames

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.

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-22-2014, 01:20 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Loop through table and create filenames

I know excel can "Save as" PDF, have you considered using that instead of a pdf printer???

Sample for excel pdf:
http://www.contextures.com/excelvbapdf.html

Should be fairly easy to obtain your data, open it in excel and save as pdf
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 10-22-2014, 02:32 AM   #3
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 555
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Loop through table and create filenames

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  
 'The previous line is where I really need help. I don't know enough VBA to get this syntax right.
Next Specialty
'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.

‘Then restore the printer settings
‘Warnings back on 

End Sub
Is this kind of thing possible in VBA?

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-22-2014, 02:50 AM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Loop through table and create filenames

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/libr...ice.12%29.aspx
It can also support to pdf, I read
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
The Following User Says Thank You to namliam For This Useful Post:
Big Pat (10-22-2014)
Old 10-22-2014, 03:20 AM   #5
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 555
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Loop through table and create filenames

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.
__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-22-2014, 04:33 AM   #6
bbwolff
Newly Registered User
 
Join Date: Oct 2013
Posts: 116
Thanks: 7
Thanked 1 Time in 1 Post
bbwolff is on a distinguished road
Re: Loop through table and create filenames

i use this to SaveAsPdf

Quote:
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

bbwolff is offline   Reply With Quote
The Following User Says Thank You to bbwolff For This Useful Post:
Thales750 (10-22-2014)
Reply

Tags
filename , loop

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VB Loop thu record ansd loop thu table pekajo Modules & VBA 2 01-17-2013 01:15 AM
Create Access Table based on filenames in a specified folder ScotWilder Modules & VBA 1 03-22-2012 07:56 AM
create a loop COMP General 13 08-11-2009 01:15 AM
create & record word document filenames jonesey Modules & VBA 3 04-16-2009 06:18 AM
Create table, loop one recordset, create two records in new, if criteria met tiberius Modules & VBA 3 04-12-2009 07:02 PM




All times are GMT -8. The time now is 09:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World