Barb said:
Hi Everyone
I have a report that needs to be saved as PDF. But I need to create a separe
PDF file for each group with file name that I keep in [FileName] in group
table. I think I need to loop thrugh report for each [GroupName] in group
table, save it as PDF and pick up [FileName] on a way.Can anyone help me with
the code?
thanks
Barb
Here's the two parts I use to generate 3500 PDF files on demand.
I have a query that gets each Consultant in my table which is the
source of the report. I populate the form with the pertinent data then
I open an access report. There are two events in the Access report one
for opening - which changes the reports.caption to a customized name (
Manager name, ect.). The reports printer driver is Adobe Distiller, and
will create the PDF file, and then close. The next Consultant is
fetched and the process is repeated.
Below is an Example of the VBA for the form side:
Sub Produce_PDFS()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
'Generates the PDF files in C:\Buffer\
'Step 1 gets the particulars of the daily PAC Trades for processing
On Error GoTo Err_Handler
'Test folder existence
myDir =
CreateObject("Scripting.FileSystemObject").FolderExists("C:\Buffer\")
If myDir = False Then
intQuest = MsgBox("C:\Buffers\ folder does not exist." & vbCrLf
& " This Folder is required to generate the PDF files." & vbCrLf & "Do
" _
& "you Wish to create this Folder to Continue?", vbYesNo)
If intQuest = 6 Then
CreateObject("Scripting.FileSystemObject").CreateFolder
("C:\Buffer\")
Else
MsgBox ("Cancelling Load due to Missing Folder")
Exit Sub
End If
End If
testpdf = Dir("C:\Buffer\*.pdf") ' Cannot have other PDF files in, it
will ruin the move
If testpdf <> "" Then
MsgBox ("There are PDF files in C:\Buffer\" & vbCrLf & "The Files must
be removed or deleted before publishing")
Exit Sub
End If
Set dbs = CurrentDb
dbs.Execute "Delete * from tblPAC_Log;"
Set rst = dbs.OpenRecordset("qrygetBM") ' Get Trades by DISTINCT Branch
Manager from tblDailyPACTrades Table
If rst.EOF And rst.BOF Then ' Table not loaded
MsgBox ("There is No Data loaded in the tblDailyPACTrades Table.
Has the csv file been imported?" & vbCrLf & "Cancelling PDF
generation")
Exit Sub
End If
Do While Not rst.EOF
Select Case Len(rst.Fields("RO")) ' Pad any RO numbers with Leading
Zeros
Case 1
Forms!frmpublishbm.txtRONUM = "00" & rst.Fields("RO")
Case 2
Forms!frmpublishbm.txtRONUM = "0" & rst.Fields("RO")
Case 3
Forms!frmpublishbm.txtRONUM = rst.Fields("RO")
Case Else
Forms!frmpublishbm.txtRONUM = rst.Fields("RO")
End Select
Forms!frmpublishbm.txtBMNumber = rst.Fields("BMNUM")
Forms!frmpublishbm.txtBMName = rst.Fields("BMNAME")
Forms!frmpublishbm.txtbmarea = rst.Fields("AREA_NAM")
Forms!frmpublishbm.txtlanguage_cde = rst.Fields("languagepref")
If rst.Fields("Languagepref") = 1 Or rst.Fields("languagepref") = 0
Then ' If BM is Unknown (number 0), then Default to English
DoCmd.OpenReport "rptDetailPACTrades", acViewPreview 'Open PDF
DoCmd.PrintOut ' Publish to c:\Buffer
DoCmd.Close acReport, "rptDetailPACTrades", acSaveNo 'Close PDF
Else ' Assume number is 2
DoCmd.OpenReport "frenchdetailpactrades", acViewPreview
DoCmd.PrintOut
DoCmd.Close acReport, "frenchdetailpactrades", acSaveNo
End If
rst.MoveNext
Loop
' Move files From C:\Buffer to the appropriate Area Folder
MsgBox ("Created all PDF Files - Make sure all Acrobat files on your
Screen closed before moving files")
Exit Sub
Err_Handler:
MsgBox (Err.Number & " " & Err.Name)
End Sub
Here is the report On Open On Close events
I also generate a record for a log table.
Private Sub Report_Close()
DoCmd.SetWarnings True
DoCmd.SelectObject acForm, "frmpublishbm"
DoCmd.Maximize
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblpac_log")
Me.Caption = "PAC REPORT RO " & Forms!frmpublishbm.txtRONUM & " " &
Forms!frmpublishbm.txtBMNumber & " " & Forms!frmpublishbm.txtBMName & "
" & Format(Forms!frmpublishbm.txtStartDate, "d-mmm-yyyy")
rst.AddNew
rst.Fields("file_name") = Me.Caption & ".pdf"
rst.Fields("ro_num") = Forms!frmpublishbm.txtRONUM
rst.Fields("bm_name") = Forms!frmpublishbm.txtBMName
rst.Fields("bm_number") = Forms!frmpublishbm.txtBMNumber
rst.Fields("date_created") = Now()
rst.Fields("pdf_produced") = True
rst.Fields("bm_language") = Forms!frmpublishbm.txtlanguage_cde
rst.Fields("area_name") = Forms!frmpublishbm.txtbmarea
rst.Fields("Report_Type") = "PAC"
rst.Update
rst.Close
DoCmd.Maximize
End Sub
