mellowest
12-07-2019, 03:44 PM
Hello,
I have a daily task of printing multiple PDF reports daily. I decided it would be a good idea to just automate the thing. I found the following code which filters to a single report and prints it to file.
Public Sub ExportFilteredReportToPDF()
Dim reportName As String
Dim fileName As String
Dim criteria As String
reportName = "Exposure_Enhancement"
fileName = "C:\Alert_Import\report_export_file.pdf"
criteria = "[Item Number] = 'Item-2019-728'"
Debug.Print criteria
DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo
End Sub
I then wrote the following to loop through all available items, and print them one by one. However, the filtering does not work and each printed PDF contains all of the items. I confirmed criteria and fileName construct properly, so I have no idea. Any help or hints would be immensely appreciated. Thank you.
Public Sub ExportFilteredReportToPDF2()
Dim reportName As String
Dim fileName As String
Dim criteria As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim DataArray As Variant
Dim outer As Long
Dim inner As Long
Dim rowString As String
strSQL = "SELECT * FROM Item_Numbers"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
DataArray = rs.GetRows(rs.RecordCount)
reportName = "Exposure_Enhancement"
For outer = LBound(DataArray, 1) To UBound(DataArray, 1)
For inner = LBound(DataArray, 2) To UBound(DataArray, 2)
rowString = DataArray(outer, inner)
fileName = """" & "C:\Item_Import\PDFSave\" & rowString & ".pdf" & """"
criteria = """" & "[Item Number] = " & "'" & rowString & "'" & """"
DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo
Next
Next
'cleanup
rs.Close
Set rs = Nothing
End Sub
I have a daily task of printing multiple PDF reports daily. I decided it would be a good idea to just automate the thing. I found the following code which filters to a single report and prints it to file.
Public Sub ExportFilteredReportToPDF()
Dim reportName As String
Dim fileName As String
Dim criteria As String
reportName = "Exposure_Enhancement"
fileName = "C:\Alert_Import\report_export_file.pdf"
criteria = "[Item Number] = 'Item-2019-728'"
Debug.Print criteria
DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo
End Sub
I then wrote the following to loop through all available items, and print them one by one. However, the filtering does not work and each printed PDF contains all of the items. I confirmed criteria and fileName construct properly, so I have no idea. Any help or hints would be immensely appreciated. Thank you.
Public Sub ExportFilteredReportToPDF2()
Dim reportName As String
Dim fileName As String
Dim criteria As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim DataArray As Variant
Dim outer As Long
Dim inner As Long
Dim rowString As String
strSQL = "SELECT * FROM Item_Numbers"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
DataArray = rs.GetRows(rs.RecordCount)
reportName = "Exposure_Enhancement"
For outer = LBound(DataArray, 1) To UBound(DataArray, 1)
For inner = LBound(DataArray, 2) To UBound(DataArray, 2)
rowString = DataArray(outer, inner)
fileName = """" & "C:\Item_Import\PDFSave\" & rowString & ".pdf" & """"
criteria = """" & "[Item Number] = " & "'" & rowString & "'" & """"
DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
DoCmd.Close acReport, reportName, acSaveNo
Next
Next
'cleanup
rs.Close
Set rs = Nothing
End Sub