I have a report that has 400 pages and I need to print (save) the individual pages to pdf format. I currently print the report in adobe acrobat professional 7.0 pdf and extract all 400 pages and rename each individual page. Ideally the reports would be saved a specific name and saved to a specific location as well. Any help greatly appreciated.

I'm a newbie when it comes to some of the code work that may be need to complete this task - so I ask for your patience in advance.

Thank You.

Welcome to UA!!

I assume each page relates to each record?

Basically what you would need to do is create a recordset, loop through each record and export each one indivually.

Can you post a sample as it will make it easier to post code that will help? Compact and repair the DB, then zip it up. it needs to be under 500K

Correct! Each page is a record (in my case each page is a US City - LA, NY, etc...)

Here's a scaled-downed verision (it's pretty big file - I'm pulling data from a lot sources) I pulled out all the sub-reports and subrpt qry, so I have just the main qry (as a table) and the report.

Thank you!

Right.... as you may or may not have suspected this is NOT as easy/small task

Here's how you do it... to get this to work you need to download Stephen Lebans ReportToPDF from http://www.lebans.com/reporttopdf.htm . Then you need to copy over the 2 .dll files into the same directory as the DB.

Then run the example...

I expect you'll have several questions about it as it's reasonabaly complicated.. but all the best things are (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

All credit for the PDF creation to Stephen Lebans... I just wrote the recordset and the function to dynamically alter the SQL of the Query. For Size and copyright reasons I have not included the .DLL files required in the attachment.

Also your structure has some issues... but from your previous comments I assume that you are puling the data from external data sources?

Thank you so much! I've played with this all day and I'm close (I hope). I ran the sample you provided (downloaded the reporttopdf) and I was able to run the sample just fine. I then imported the query, form and the modules and when I run the report for all markets it's running all 427 reports and saving it as New York, NY with all 427 markets in 1 pdf file, then its running another report of 427 and naming it Los Angeles again 1 file, etc... It's not picking up the break between markets.

In my master query I'm using the MSA Code (not MSA Full Name) - that I tie all my market data sources together with - does that matter? Or is it something in the loop?

Thank you so much!

Good News! I think I figured it out. I changed the record source in my report to the Qry_S_RunReport and it works. (How does it know to pull in all that data?)

Any thoughts on why some of the "lines" on the report are not showing up in the abode pdf? The text box lines are showing up, but not the "straight" lines I used to separate some of data.

Also - I noticed that the pdf files are larger than running these one at time (just curious what's making the files bigger - can I do something with settings to pdf from Access to compress the files? No biggie if not)

Again thank you so much - this is pretty awesome!

Dale

Not sure.

it's probably a feature/downside of using the methods Stephen Lebans has used.



Basically in the download there is a function


This function changes the SQL of the Query to the current record (as that is the data which is passed to the query with this line)



It then exports the report and then moves to the next record and repeats the process again.



I think this is a limitation of Stephen Lebans PDF Creator. To get round it replace the lines with thin rectangles which emulate lines. I believe that works...

If you have more questions then just post back.

Thank You So much! This will really save me some time!

Another Question: I have a similar report by MSA (city) in which two of my sub reports flow onto the next page, so the total report is over 1000 pages. So in the bigger Markets (LA, NY, Chicago, etc...) these individual reports are 10 to 15 pages long (others may be one or two). Do you think this process I just used will work for individually printing/saving the 400+ Msa's? The MSA name/code is assoicated with each page (if that helps?) Just curious.

Again thank you so much!

You're welcome

yes it should do.

As long as you pass over the record identify for the main report the sub reports will work as normal.

You're welcome

yes it should do.

As long as you pass over the record identify for the main report the sub reports will work as normal.

Hi Danny,

You were such a great help recently, that I thought I would pick your brian again. I've transfered the Print to PDF format as mentioned above, but I'm getting this error message. I'm guessing its something really silly that I overlooked. If you have any thoughts I would greatly appreicate it.

After I "click" on the "export reports"

An Error Occured
Error Code: 3265
Error description: Item cannot be found in the collection corresponding to the requested name or ordinal.

I can't figure out what item isn't corresponding? (if that's what I should be looking for?)

Any help would be great!

Nevermind - I figured it out - I knew it was something silly (I had Change Query Name misspelled - Ugh....)

Hope all is well - thank you!

Dale

Hello, am interested in getting this example to work - have Access 2003 and have the LeBans files and the example in the same folder. Opening up form and clicking control causes database to crash and a backup database to be created.

I need to add a WHERE clause to Leban's method in Access 2000.

The attachement example that Danny Seager has posted is apparently in Access 2003. So I can't open this file

I have tried to infer the method from the small bits of code listed above. But I am missing something.

Thanks for any help.

I found some good advice in a UK forum. There was some indication that Stephan Lebans was adding this to his site, but I couldn’t find it. (Probably didn’t search hard enough.)

Since I had to piece this together, I thought I would summarize of what works for me. Hope this helps somebody.

1) Add Stephan Lebans’ code to a general module. (See http://www.lebans.com/ and download the Report to PDF.)
1a) Add the two DLLs that Lebans provides to the same folder in which the mdb resides.

2) At the end of the general module, add this subroutine

Public Sub Report_SetReportFilter(pReportName, pFilter)
Dim rpt As Report
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.Filter = pFilter
rpt.FilterOn = True
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
End Sub

3) Now in my code, when I need to make a PDF file, I add these lines:
R_name = [Name of the report to run]
R_sort = [WHERE clauses for how you want to filter the report (leave off the ‘WHERE’]
O_name = [Path and name of the output file]
Call Report_SetReportFilter(R_name, R_sort) 'save the report filter
Call ConvertReportToPDF(R_name, vbNullString, O_name, False, False)
Call Report_SetReportFilter(R_name, "") ' clear the report filter


What happens is:
The Report_SetReportFilter subroutine opens the report in design mode, changes the filter definition, turns the filter on and saves the report.
The ConvertReportToPDF subroutine then uses the Lebans method to create the PDF file. (The ‘False, False’ set parameters that control how and if the PDF is opened after created. See the Lebans documents for descriptions.)

I experience the same as dannyseager, this test file causes Access to crash after it has exported one pdf file. Do anyone know reason and how to solve it?

Brgds, Magnus