By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

How to export a query to excel based on a filter string?

P: 16
Hi All,

I use the code below in my form to filter data.
But I also need to export the data to excel. So I'd like to create a temporary query based on the strWhere and export that query to excel.

Could anyone modify the code for me?

cheers

Michiel


:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.     Dim strWhere As String
  3.     Dim lngLen As Long
  4.     Dim ctl As Control
  5.  
  6.     If Not IsNull(Me.txtFilterMainName) Then
  7.         strWhere = strWhere & "([supplier] Like ""*" & Me.txtFilterMainName & "*"") AND "
  8.     End If
  9.  
  10.     lngLen = Len(strWhere) - 5
  11.     If lngLen <= 40 Then
  12.         MsgBox "No criteria", vbInformation, "Nothing to do."
  13.  
  14.         For Each ctl In Me.Section(acHeader).Controls
  15.         Select Case ctl.ControlType
  16.         Case acTextBox, acComboBox
  17.             ctl.Value = Null
  18.         Case acCheckBox
  19.             ctl.Value = False
  20.         End Select
  21.     Next
  22.      Me.Filter = "(false)"
  23.     Me.FilterOn = True
  24.     Else
  25.         strWhere = Left$(strWhere, lngLen)
  26.         'Debug.Print strWhere
  27.  
  28.         Me.Filter = strWhere
  29.         Me.FilterOn = True
  30.     End If
  31.  
May 14 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,636
Hi All,

I use the code below in my form to filter data.
But I also need to export the data to excel. So I'd like to create a temporary query based on the strWhere and export that query to excel.

Could anyone modify the code for me?

cheers

Michiel


:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.     Dim strWhere As String
  3.     Dim lngLen As Long
  4.     Dim ctl As Control
  5.  
  6.     If Not IsNull(Me.txtFilterMainName) Then
  7.         strWhere = strWhere & "([supplier] Like ""*" & Me.txtFilterMainName & "*"") AND "
  8.     End If
  9.  
  10.     lngLen = Len(strWhere) - 5
  11.     If lngLen <= 40 Then
  12.         MsgBox "No criteria", vbInformation, "Nothing to do."
  13.  
  14.         For Each ctl In Me.Section(acHeader).Controls
  15.         Select Case ctl.ControlType
  16.         Case acTextBox, acComboBox
  17.             ctl.Value = Null
  18.         Case acCheckBox
  19.             ctl.Value = False
  20.         End Select
  21.     Next
  22.      Me.Filter = "(false)"
  23.     Me.FilterOn = True
  24.     Else
  25.         strWhere = Left$(strWhere, lngLen)
  26.         'Debug.Print strWhere
  27.  
  28.         Me.Filter = strWhere
  29.         Me.FilterOn = True
  30.     End If
  31.  
Here is a Code Template that should help you out:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, qryDEF As DAO.QueryDef
  2.  
  3. Set MyDB = CurrentDb()
  4.  
  5. 'If qryTemp exists, DELETE it
  6. For Each qryDEF In MyDB.QueryDefs
  7.   If qryDEF.Name = "qryTemp" Then
  8.     MyDB.QueryDefs.Delete qryDEF.Name
  9.       Exit For
  10.   End If
  11. Next
  12.  
  13. 'Create the Query using an SQL String and a modification of strWhere 
  14. '(don't forget the WHERE Clause)
  15. Set qryDEF = MyDB.CreateQueryDef("qryTemp", "<From SQL String with strWhere>")
  16.  
  17. 'E-Mail it
  18. DoCmd.SendObject acSendQuery, "qryTemp", "<output format>", "<To>", "<Cc>", "<Bcc>", "<Subject>", "<message text>", "<edit message>", "<template file>"
May 14 '07 #2

P: 16
OK thanks!

I expanded the code a little and I tried to apply the template.
However, if I run to code it is unable to find the strWhere. What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command55_Click()
  2. Dim MyDB As DAO.Database, qryDEF As DAO.QueryDef
  3.     Dim strWhere As String
  4.     Dim lngLen As Long
  5.     Dim ctl As Control
  6.     Set MyDB = CurrentDb()
  7. For Each qryDEF In MyDB.QueryDefs
  8.   If qryDEF.Name = "qryTemp" Then
  9.     MyDB.QueryDefs.delete qryDEF.Name
  10.       Exit For
  11.   End If
  12. Next
  13.  
  14.     If Not IsNull(Me.txtfilterphase) Then
  15.         strWhere = strWhere & "([projectstage] = """ & Me.txtfilterphase & """) AND "
  16.     End If
  17.  
  18.     strWhere = strWhere & "([projectstage] <> """ & "Inactive" & """) AND "
  19.  
  20.     If Not IsNull(Me.txtFilterMainName) Then
  21.         strWhere = strWhere & "([supplier] Like ""*" & Me.txtFilterMainName & "*"") AND "
  22.     End If
  23.        If Me.txtfilterstatus = "Green" Then
  24.         strWhere = strWhere & "([Status]= """ & "1" & """) AND "
  25.     ElseIf Me.txtfilterstatus = "Amber" Then
  26.         strWhere = strWhere & "([Status]= """ & "2" & """) AND "
  27.     ElseIf Me.txtfilterstatus = "Red" Then
  28.         strWhere = strWhere & "([Status]= """ & "3" & """) AND "
  29.     End If
  30.  
  31.     If Me.txtFilterBG = "DE" Then
  32.         strWhere = strWhere & "([DE]= """ & "yes" & """) AND "
  33.     ElseIf Me.txtFilterBG = "DMC" Then
  34.         strWhere = strWhere & "([DMC]= """ & "yes" & """) AND "
  35.     ElseIf Me.txtFilterBG = "DFS" Then
  36.         strWhere = strWhere & "([DFS]= """ & "yes" & """) AND "
  37.     ElseIf Me.txtFilterBG = "DMM" Then
  38.         strWhere = strWhere & "([DMM]= """ & "yes" & """) AND "
  39.         ElseIf Me.txtFilterBG = "ALL" Then
  40.         strWhere = ""
  41.     End If
  42.    If Me.txtfilteraction = "Project team" Then
  43.         strWhere = strWhere & "([Processowner]= """ & "1" & """) AND "
  44.     ElseIf Me.txtfilteraction = "Contract Management" Then
  45.         strWhere = strWhere & "([Processowner]= """ & "2" & """) AND "
  46.     ElseIf Me.txtfilteraction = "Vendor" Then
  47.         strWhere = strWhere & "([Processowner]= """ & "3" & """) AND "
  48.     ElseIf Me.txtfilteraction = "CDMP" Then
  49.         strWhere = strWhere & "([Processowner]= """ & "4" & """) AND "
  50.     End If
  51.  
  52.     lngLen = Len(strWhere) - 5
  53.     strWhere = Left$(strWhere, lngLen)
  54.  
  55. Set qryDEF = MyDB.CreateQueryDef("qryTemp", "SELECT [data table].dfs, [data table].dmc FROM [data table] WHERE strWhere")
  56. DoCmd.TransferSpreadsheet acExport, 8, "qryTemp", "c:\test.xls", True
  57.  
  58. End Sub
May 15 '07 #3

ADezii
Expert 5K+
P: 8,636
OK thanks!

I expanded the code a little and I tried to apply the template.
However, if I run to code it is unable to find the strWhere. What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command55_Click()
  2. Dim MyDB As DAO.Database, qryDEF As DAO.QueryDef
  3.     Dim strWhere As String
  4.     Dim lngLen As Long
  5.     Dim ctl As Control
  6.     Set MyDB = CurrentDb()
  7. For Each qryDEF In MyDB.QueryDefs
  8.   If qryDEF.Name = "qryTemp" Then
  9.     MyDB.QueryDefs.delete qryDEF.Name
  10.       Exit For
  11.   End If
  12. Next
  13.  
  14.     If Not IsNull(Me.txtfilterphase) Then
  15.         strWhere = strWhere & "([projectstage] = """ & Me.txtfilterphase & """) AND "
  16.     End If
  17.  
  18.     strWhere = strWhere & "([projectstage] <> """ & "Inactive" & """) AND "
  19.  
  20.     If Not IsNull(Me.txtFilterMainName) Then
  21.         strWhere = strWhere & "([supplier] Like ""*" & Me.txtFilterMainName & "*"") AND "
  22.     End If
  23.        If Me.txtfilterstatus = "Green" Then
  24.         strWhere = strWhere & "([Status]= """ & "1" & """) AND "
  25.     ElseIf Me.txtfilterstatus = "Amber" Then
  26.         strWhere = strWhere & "([Status]= """ & "2" & """) AND "
  27.     ElseIf Me.txtfilterstatus = "Red" Then
  28.         strWhere = strWhere & "([Status]= """ & "3" & """) AND "
  29.     End If
  30.  
  31.     If Me.txtFilterBG = "DE" Then
  32.         strWhere = strWhere & "([DE]= """ & "yes" & """) AND "
  33.     ElseIf Me.txtFilterBG = "DMC" Then
  34.         strWhere = strWhere & "([DMC]= """ & "yes" & """) AND "
  35.     ElseIf Me.txtFilterBG = "DFS" Then
  36.         strWhere = strWhere & "([DFS]= """ & "yes" & """) AND "
  37.     ElseIf Me.txtFilterBG = "DMM" Then
  38.         strWhere = strWhere & "([DMM]= """ & "yes" & """) AND "
  39.         ElseIf Me.txtFilterBG = "ALL" Then
  40.         strWhere = ""
  41.     End If
  42.    If Me.txtfilteraction = "Project team" Then
  43.         strWhere = strWhere & "([Processowner]= """ & "1" & """) AND "
  44.     ElseIf Me.txtfilteraction = "Contract Management" Then
  45.         strWhere = strWhere & "([Processowner]= """ & "2" & """) AND "
  46.     ElseIf Me.txtfilteraction = "Vendor" Then
  47.         strWhere = strWhere & "([Processowner]= """ & "3" & """) AND "
  48.     ElseIf Me.txtfilteraction = "CDMP" Then
  49.         strWhere = strWhere & "([Processowner]= """ & "4" & """) AND "
  50.     End If
  51.  
  52.     lngLen = Len(strWhere) - 5
  53.     strWhere = Left$(strWhere, lngLen)
  54.  
  55. Set qryDEF = MyDB.CreateQueryDef("qryTemp", "SELECT [data table].dfs, [data table].dmc FROM [data table] WHERE strWhere")
  56. DoCmd.TransferSpreadsheet acExport, 8, "qryTemp", "c:\test.xls", True
  57.  
  58. End Sub
It is just a matter of incorrect syntax. In your version of the code, the WHERE Clause is looking for the 'Literal String' strWhere, instead of the String Variable strWhere. The proper syntax would be:
Expand|Select|Wrap|Line Numbers
  1. Set qryDEF = MyDB.CreateQueryDef("qryTemp", "SELECT [data table].dfs, [data table].dmc FROM [data table] WHERE " & strWhere)
May 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.