Experts:
I have a VBA routine that sequentially (one after the other) N number of PDF files based on any or all chosen values listed in a listbox. This routine works perfect! Please see VBA below:
Code:
Option Compare Database
Option Explicit
Private Sub cmdExportReport_Click()
'Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim ReportPath As String
Dim ReportPathMsgBox As String
Dim ReportFileName As String
Dim OutputPathFileName As String
Dim NumReports As Integer
'Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT")
'Reports are saved to the below file path -- upon change, ensure to update both **actual report path** AND **message box report path**
ReportPath = "C:\Users\dellc\Reports\CFT Ownership\CFT Ownership Report - "
ReportPathMsgBox = "C:\Users\dellc\Reports\CFT Ownership"
'Loop through the selected items in the list box and build a text string
If Me!lstCFTOwners.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstCFTOwners.ItemsSelected
strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstCFTOwners.ItemData(varItem) & "'"
'Build the new SQL statement incorporating the string
strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, ...
'long SQL statement... no need to include this for purposes of the question
.
'Apply the new SQL statement to the query
qdf.SQL = strSQL
'Replaces any potential forward slashes in the N-Code (e.g., N2/N39) since "/" cannot be used as part of the filename
ReportFileName = Replace(Me!lstCFTOwners.ItemData(varItem), "/", "_") & ".pdf"
OutputPathFileName = ReportPath & ReportFileName
'Outputs **all** file(s) to specified file path
'DoCmd.OutputTo acOutputReport, "R51_CFT_Ownership_Report_Gonzalez", acFormatPDF, OutputPathFileName, False
'Outputs only file(s) **with records** to specified file path
If DCount("*", "Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT") > 0 Then
DoCmd.OutputTo acOutputReport, "R51_CFT_Ownership_Report_Gonzalez", acFormatPDF, OutputPathFileName, False
NumReports = NumReports + 1
End If
Next varItem
'Throw message box upon successful extraction of the PDFs
MsgBox NumReports & " CFT Ownership reports were stored at the following location: " & ReportPathMsgBox, vbInformation, "Information"
Else
'Throw message box in the event user did not select any N-Codes prior to report execution
MsgBox "Please select one or more N-Codes!", vbInformation, "Information"
End If
'Empty the memory
Set qdf = Nothing
Set db = Nothing
End Sub
At this time, however, I'd like to slightly tweak the existing VBA. Currently, a PDF filename has the following format: CFT Ownership Report - N2_N39.pdf
This is based on VBA line #16 where "ReportPath = "C:\Users\dellc\Reports\CFT Ownership\CFT Ownership Report - "
Now, as part of the code modification, I'd like to add the date (YYYY-MM-DD) in parenthesis at the end of the filename. So, the automatically generated filename should now be, e.g.: "CFT Ownership Report - N2_N39 (2019-08-23).pdf" if extraced today. Naturally, the date must be based on the computer's date time stamp.
My question: How should the VBA above be modified so that the date of the extraction/file generation will be added in parenthesis at the end?
Thank you,
EEH