Print to PDF loop...files being overwritten

[Excel_Kid1081]

Hello-

I currently have the code below that loops through a table with named reports and prints them to PDF. The code works fine except each report is overwritten by the next report in the loop because all of the reports are being saved under the same name.

Sub LoopReports()
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim RName As Variant

'Adobe set-up
Dim tmpPrinter As Printer
Dim rptName As String
Set tmpPrinter = Application.Printer 'Default Printer
Set Application.Printer = Application.Printers("Adobe PDF") 'Changes to Adobe

Set rs = CurrentDb.OpenRecordset("SELECT Report_Name FROM tblReports")

Do Until rs.EOF
rptName = rs!Report_Name
DoCmd.SetWarnings False
DoCmd.OpenReport rptName, acViewNormal
DoCmd.Close acReport, rptName, acNo
rs.MoveNext
Loop


rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print strSQL
Set tmpPrinter = Application.Printer
Set Application.Printer = Application.Printers(0) ' Restore Default printers


End Sub

How would I alter this code to get each report in the loop to be saved separately with their own report names?

Thanks!

EK

 

[jal]

Maybe try this - let's assume that the Adobe printer is saving the pdf to C:\Pdf.pdf.
Dim fso as object
set fso = CreateObject("Scripting.FileSystemObject")

Then in your loop

Dim pathToDest as string
pathToDest = "C:\" & rs("ReportName") & ".pdf"
fso.CopyFile("C:\Pdf.pdf", pathToDest)

Not sure if that will work. Let us know.

 

[Excel_Kid1081]

Thanks for the reply! Code is getting hung up on:

fso.CopyFile("C:\Pdf.pdf", pathToDest)

Syntax Error

Not sure how to manipulate these.

Thanks!!

EK

 

[jal]

Sorry, I am so used to VB.Net which always uses parentheses. Try removing those.

 

[jal]

By the way, have you found out where the printer placing the PDF file? Obviously you can't use C:\Pdf.pdf if that's not where it is. (I was just using that as an example - you need to put in the real path to the pdf).

 

[Excel_Kid1081]

OK. Still bumping my head on this one.... this is what I got

Set fso = CreateObject("Scripting.FileSystemObject")

Do Until rs.EOF
rptName = rs!Report_Name
DoCmd.SetWarnings False
DoCmd.OpenReport rptName, acViewNormal
pathToDest = "D:\Documents and Settings\My Documents"
SourceFile = rs(rptName) & ".pdf"
fso.copyfile SourceFile, pathToDest, False
DoCmd.Close acReport, rptName, acNo
rs.MoveNext
Loop

For some reason the code is giving me a RunTime Error 3265 "item Not Found in Collection" It is looking like the SourceFile Variable is coming through blank when I step through it but I don't understand why. This is my first time with the FileSystemObject in VBA.

Thanks!

EK

 

[jal]

OK. Still bumping my head on this one.... this is what I got

Set fso = CreateObject("Scripting.FileSystemObject")

Do Until rs.EOF
rptName = rs!Report_Name
DoCmd.SetWarnings False
DoCmd.OpenReport rptName, acViewNormal
pathToDest = "D:\Documents and Settings\My Documents"
'Here it should probably be rs(Report_Name)
SourceFile = rs(rptName) & ".pdf"
fso.copyfile SourceFile, pathToDest, False
DoCmd.Close acReport, rptName, acNo
rs.MoveNext
Loop

Again, you haven't told me where the pdf printer is saving the PDF file to. You said it's being overwritten. WHERE is it, as to be overwritten?

 

[Excel_Kid1081]

Printer is saving the PDF in My Documents.....However, I tried your code modification and still getting errors on the same line....I still don't get why when I set SourceFile equal to the code following it and then step through it SourceFile is showing me a blank variable?? Thanks for your help!

 

[jal]

First of all, did you make the change that I highlighted in red?

Secondly, you still haven't told me the path to the pdf. That path should be your SOURCE file, NOT your destination file.

On my computer, the path to MyDocuments is this:

C:\Documents and Settings\JAL\My Documents

if the pdf file is called SomeFile.PDF then you would write:

SourceFile = "C:\Documents and Settings\JAL\My Documents\SomeFile.PDF"

The dest file could be something like this:
pathToDest = "C:\" & rs(Report_Name)