Results 1 to 4 of 4
  1. #1
    seb is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    73

    export qry to excel and naming file with date range and idcode

    Hello




    On 03-25-2017 sneuberg gently helped me with above code.
    With this code I export a query to excel. If for example I search by day1 20_02_2018 and day2 20_02_2018 the code assign the exported file name as 20_02_2018_qry_01.

    HTML Code:
    Private Sub cmb_export_excel_bydatesrange_plot_Click()Dim filename1 As String
    If DCount("*", "qry_01") <> 0 Thenfilename1 = "C:\Path\" & Replace(Me.txtday1, "/", "_") & "_qry_01.xlsx"        DoCmd.OutputTo acOutputQuery, "qry_01", acFormatXLSX, filename1, False        MsgBox "Query already exported to Excel"
        Else        MsgBox "This range has no records"        DoCmd.Close acQuery, "qry_01"    End IfEnd Sub
    I need the code to include in the exported file name the plot code such: 20_02_2018_qry_01_6


    On the other hand, when user enters to dates and plot code such as:
    Day1 05/02/2018 and Day2 20/02/2018 and plot 2


    In the exported query I need the file name as: 05_02_2018- 20_02_2018_2.


    I really appreciated your help

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Please properly format your posted code.

    You can build the filename1 value with any concatenated data you want. Build incrementally.

    Use If Then or an IIf() to conditionally include the ending date.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    seb is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    73
    June7. Many thanksYou mean a code something like this:


    If day1 = Day2 Then
    filename1 = "C:\Path" & Replace(Me.txtday1, "/", "_") & "_qry_01.xlsx"
    DoCmd.OutputTo acOutputQuery, "qry_01", acFormatXLSX, filename1, False
    MsgBox "Query already exported to Excel"
    If day1 <> Dy2 Then
    .
    .
    .
    End If


    I am not sure what I have to write above.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, something like that. Test it.

    Only use Else.

    However, will not work if either of the controls has Null. Deal with Null

    If day1 & "" = day2 & "" Then
    ...
    Else
    ...
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Date out of range error on Excel Export
    By RCG in forum Import/Export Data
    Replies: 1
    Last Post: 05-09-2016, 05:05 PM
  2. Replies: 2
    Last Post: 04-07-2016, 02:41 AM
  3. Replies: 7
    Last Post: 01-08-2014, 10:36 AM
  4. Replies: 3
    Last Post: 06-18-2013, 01:14 PM
  5. Replies: 5
    Last Post: 02-24-2012, 11:02 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