Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Passing criteria to query and outputting to excel?

    I have created a query with no criteria and a report that displays the query. I then wrote code to accept user input via a multiple select list box, which passes the criteria to the query, and open the report. Works great.

    What I can't figure out now is how to do the same thing, but output to excel instead of opening a report. I know once the report is open, you can hit the "Analyze with Excel" button on the toolbar, but I want a button on the form to to that automatically. Any ideas? Here is my code fore the report:

    Code:
    Private Function GetCriteria() As String
       Dim stDocCriteria As String
       Dim VarItm As Variant
       For Each VarItm In ListFilter.ItemsSelected
      stDocCriteria = stDocCriteria & "[FF] = '" & ListFilter.Column(0, VarItm) & "' OR "
       Next
       If stDocCriteria <> "" Then
      stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
       Else
      stDocCriteria = "True"
       End If
       GetCriteria = stDocCriteria
    End Function
    
    Private Sub previewreport_Click()
    DoCmd.OpenReport "rptInventory Detail Current Quarter - Report", acViewPreview, , GetCriteria()
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way that would preserve your current methodology would be to open the report filtered (hidden if desired), export it, then close it again.
    Paul

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    One way that would preserve your current methodology would be to open the report filtered (hidden if desired), export it, then close it again.
    I am ok with this. But not sure how to go about it. What code would i use, and where?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    "Where" is wherever is appropriate to your application (a button?). Code along the lines of:

    DoCmd.OpenReport...
    DoCmd.OutputTo...
    DoCmd.Close...

    You can probably also use TransferSpreadsheet instead of OutputTo. You could also use this method to filter the report:

    Emailing a different report to each recipient

    tweaking your function to set a public variable to the string, and having that code use the variable.
    Paul

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    "Where" is wherever is appropriate to your application (a button?). Code along the lines of:

    DoCmd.OpenReport...
    DoCmd.OutputTo...
    DoCmd.Close...

    You can probably also use TransferSpreadsheet instead of OutputTo. You could also use this method to filter the report:

    Emailing a different report to each recipient

    tweaking your function to set a public variable to the string, and having that code use the variable.
    Perfect, thank you

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help.
    Paul

Tags for this Thread

Posting Permissions

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