Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need to add "date" to automated PDF output file

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this for a start:

    ReportFileName = Replace(Me!lstCFTOwners.ItemData(varItem), "/", "_") & Format(Date(), "yyyy-mm-dd") & ".pdf"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Great start... report comes back as
    CFT Ownership Report - N01H2019-08-23.pdf"

    I changed code to: ReportFileName = Replace(Me!lstCFTOwners.ItemData(varItem), "/", "_") & " - " & Format(Date, "yyyy-mm-dd") & ".pdf"

    and now report comes out as: "CFT Ownership Report - N01H - 2019-08-23.pdf"

    PERFECT! Thank you, Paul.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem! I figured you'd see how to add the parentheses if you want them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  2. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  3. Replies: 2
    Last Post: 11-26-2012, 11:12 PM
  4. Replies: 2
    Last Post: 09-29-2012, 11:22 PM
  5. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums