Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts

    A button to export to Excel (Access 2016)

    Greetings,

    I created a form whereby users answer to parameter values and press ‘Run Query” and pass those parameter values to a report.
    I used the following for ‘Run Query’ button:
    Code:
    DoCmd.OpenReport "MrReportName", acViewPreview
    The above works fine. What I’m trying to achieve is the following:
    Once users previewed the filtered data, I want them to be able to export the result to excel. To achieve this, I created another button ‘Export’ and use the following syntax:

    Code:
    DoCmd.OutputTo acOutputQuery, "MyQueryName ", acFormatXLS, , True
    The above works well. The problem is that it doesn’t reflect the filtered data (the report displays everything).
    I also attempted to use the built-in export to excel wizard for the command button, but I did not see any option to export data to excel.

    TIA,

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,474
    Thanks
    1
    Thanked 41 Times in 40 Posts
    You must modify the query directly. Here's how I do it.

    I first start with the base SQL string for the report, which I store in a table (using query name as the PK). Example:
    "SELECT tblStuff.* FROM tblStuff WHERE 1=1"

    I load the SQL for the query I want into a string variable, let's say strSQL.

    The important part is "1=1". I form a string with the where conditions I need. Simplistically it may come down to:
    strWHERE="OrderDate>=#01/01/2017# AND CustomerID=1000"

    Then a simple replace:
    strSQL = Replace(strSQL,"1=1",strWhere)

    Then finally:
    CurrentDB.QueryDefs("you query name here").SQL = strSQL

    Now I can run the export. This technique is also very helpful in outputting a report to PDF. Again, you can't use filters; you must modify the recordsource of the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mark,

    Thanks for the reply post.

    Can you please explain the use of WHERE 1 = 1, and how to modify the query directly?
    My table name is tbl_Cases_all and my query is qry_Cases_All.

    TIA,

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,474
    Thanks
    1
    Thanked 41 Times in 40 Posts
    The "1=1" is merely a "placecard" holder. That is, the query will still run with it in there, but it gives me something to look for when I use Replace().

    The whole idea is that every time you run the report, you will have different parameters. Maybe every time you usually will have a date range, but sometimes you may run it only for a specific customer, or maybe only for customers in a specific state, or whatever.

    So let's say you have "rptSomething". I create a query "qryrptSomething" for its recordsource. Each time I need to run that report (or the query separately), I just have to make sure I modify the .SQL property of the query. I have a table in which I store my basic SQL statement for "qryrptSomething" (as well as all my other such queries).

    All reports are executed from a form, in which the user will make all the selections (date range, customer if any, etc.); and then I usually have buttons for "Print", "Preview", "Export To PDF". and maybe "Export to Excel". Which button is selected calls the same code that properly populates the query. It starts like this:

    strSQL = DLookup("qryrptSomething","tsysSQL","QueryName='qr yrptSomething'")

    Note that tsysSQL is my table name, and QueryName is the PK. The SQL contains "1=1" in the WHERE statement.

    I then populate a variable (call it varWhere) with the options the user selected, which depending on the situation might come down something like:
    "OrderDate Between #01/01/2017# AND #03/31/2017# AND CustomerID=12345"

    Then it is just:

    strSQL = Replace(strSQL, "1=1", varwhere) 'this substitutes that WHERE clause above for "1=1"
    (Note: if user made no selections then I don't have to do this substitution! The query runs just fine with 1=1)

    Currentdb.QueryDefs("qryrptSomething").SQL = strSQL 'This changes the query's SQL.

    Then you can run the report, export the query, or whatever.

    Hope this helps.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mark, thank you for the detail explanation.

    The example you gave above is exactly what I’m currently working on. I successfully created the form for users to select from dropdowns, select between date intervals etc. and click ‘Run Query’ button to preview the report. If users make selections, the report will limit the display to selections made only. Otherwise, the report displays everything. So far so good.

    I also created another button to export the result to excel ‘Export to Excel’ button.

    1. Do I change my codes in the properties of ‘Export to Excel’ button?

    Modify the query directly. Here's how I do it.

    I first start with the base SQL string for the report, which I store in a table (using query name as the PK). Example:
    "SELECT tblStuff.* FROM tblStuff WHERE 1=1"
    My query ‘qry_Cases_all’ is based on multiple tables: tblCases, tblStatus, tblInvestigator and my report ‘rpt_Cases_all’

    2. How do I apply your example above in my situations?

    TIA,

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,474
    Thanks
    1
    Thanked 41 Times in 40 Posts
    It doesn't matter what the user selected to do: Print, Preview, Output to PDF, Export Query. You call the same routine to modify the SQL behind your 'qry_Cases_all'.

    You could completely create the SQL in code each time, but that can be quite a bit of code if you have a very complicated SQL statement. My "1=1" technique is useful because I can see (and test) the full SQL regardless of how complicated it is, so I know my joins are correct and I am returning the fields I need all before I apply the Where conditions. It is also much easier to make changes. I just copy the current SQL (with the 1=1) from it stored location to a new query, make the changes (and test), then copy the new SQL back to the stored location.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mark,

    Here is the code I used for the properties of the ‘run query’ button. This works very nicely as planned.
    Code:
    Private Sub cmdRunQuery_Click()
    
    Dim strWhere As String
    
    strWhere = " 1=1 "
    
    If Not IsNull(Me.txtBeginRefDT) Then
        strWhere = strWhere & " AND [REFDT] >= #" & txtBeginRefDT & "# "
    End If
    If Not IsNull(Me.txtEndRefDT) Then
        strWhere = strWhere & " AND [REFDT] <= #" & txtEndRefDT & "# "
    End If
    If Not IsNull(Me.txtBeginAssignDT) Then
        strWhere = strWhere & " AND [ASSIGNDT] >= #" & txtBeginAssignDT & "# "
    End If
    If Not IsNull(Me.txtEndAssignDT) Then
        strWhere = strWhere & " AND [ASSIGNDT] <= #" & txtEndAssignDT & "# "
    End If
    If Not IsNull(Me.cboStatus) Then
          strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "
    End If
    If Not IsNull(Me.cboInvesgigator) Then
             strWhere = strWhere & " AND [USERNM] = """ & Me.cboInvesgigator & """ "
    End If
    If Not IsNull(Me.cboInvestigatorType) Then
          strWhere = strWhere & " AND [INVTYPE] = """ & Me.cboInvestigatorType & """ "
    End If
    If Not IsNull(Me.txtBeginCloseDT) Then
        strWhere = strWhere & " AND [CLOSEDT] >= #" & txtBeginCloseDT & "# "
    End If
    If Not IsNull(Me.txtEndCloseDT) Then
        strWhere = strWhere & " AND [CLOSEDT] <= #" & txtEndCloseDT & "# "
    End If
    If Not IsNull(Me.cboCloseReason) Then
        strWhere = strWhere & " AND [CLSREASON] = " & Me.cboCloseReason
    End If
    If Not IsNull(Me.txtBeginProsReferredDT) Then
        strWhere = strWhere & " AND [PROSDT] >= #" & txtBeginProsReferredDT & "# "
    End If
    If Not IsNull(Me.txtEndProsReferredDT) Then
        strWhere = strWhere & " AND [PROSDT] <= #" & txtEndProsReferredDT & "# "
    End If
    If Not IsNull(Me.cboRefAgency) Then
             strWhere = strWhere & " AND [AGENCYNM] = """ & Me.cboRefAgency & """ "
    End If
    If Not IsNull(Me.txtBeginAcceptDT) Then
        strWhere = strWhere & " AND [PROSACCPTDT] >= #" & txtBeginAcceptDT & "# "
    End If
    If Not IsNull(Me.txtEndAcceptDT) Then
        strWhere = strWhere & " AND [PROSACCPTDT] <= #" & txtEndAcceptDT & "# "
    End If
    If Not IsNull(Me.txtBeginDeclineDT) Then
        strWhere = strWhere & " AND [PROSREJDT] >= #" & txtBeginDeclineDT & "# "
    End If
    If Not IsNull(Me.txtEndDeclineDT) Then
        strWhere = strWhere & " AND [PROSREJDT] <= #" & txtEndDeclineDT & "# "
    End If
    If Not IsNull(Me.txtBeginRecovLetteSentDT) Then
        strWhere = strWhere & " AND [LETTERDT] >= #" & txtBeginRecovLetteSentDT & "# "
    End If
    If Not IsNull(Me.txtEndRecovLetterSentDT) Then
        strWhere = strWhere & " AND [LETTERDT] <= #" & txtEndRecovLetterSentDT & "# "
    End If
    If Not IsNull(Me.txtBeginFinalSetlmtDT) Then
        strWhere = strWhere & " AND [FINALDT] >= #" & txtBeginFinalSetlmtDT & "# "
    End If
    If Not IsNull(Me.txtEndFinalSetlmtDT) Then
        strWhere = strWhere & " AND [FINALDT] <= #" & txtEndFinalSetlmtDT & "# "
    End If
    
    Debug.Print strWhere   'show me the value
    
    DoCmd.OpenReport "rpt_CASES_All", acViewPreview, , strWhere
    
    
    End Sub
    I’m not familiar with DAO changing SQL properties etc.
    What I’m still struggling is to export the same report to excel based on user selection.

    TIA,

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,653
    Thanks
    3
    Thanked 65 Times in 64 Posts
    You can export a report to Excel by right-clicking on it in display mode, but the formatting and page info will show up on it. The better solution is to use code to modify the underlying query so it has the constraints you are applying in the filter. If nobody jumps in, I'll try to find an example you could use.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wendell,
    That is correct, but:
    1. Users do not have access to export this way
    2. I created a button to export and used the following code:

    DoCmd.OutputTo acOutputQuery, "MyQueryName ", acFormatXLS, True
    The above works fine, but it exports everything. As you stated, it doesn’t limit to the constraints that are applied.

    TIA,

  10. #10
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    147
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wendel,

    I did find an example to change the SQL property of a saved query (courtesy of Duane) attached.
    But, I’m still struggling how to apply his example to my situation: how to set up the SQL, or calling the export etc.

    TIA,
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •