tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,907
- Office Version
- 365
- 2019
- Platform
- Windows
Further to my post here:
the code that does work is as follows:
But if I have multiple criteria, such as:
would it be quicker to do a For Next Loop, as in:
Thanks
Code:
https://www.mrexcel.com/board/threads/filter-data-using-sql.1158918/#post-5622447
the code that does work is as follows:
Code:
Sub GetData()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strcon As String
Dim strSQL As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;" & _
"HDR=Yes;" & _
"IMEX=1;" & _
"MaxScanRows=0"";"
cn.Open ConnectionString:=strcon
strSQL = "SELECT * " & _
"FROM [DataSheet$] " & _
"WHERE [DataSheet$].[Fruit] =""orange"""
rs.Open Source:=strSQL, _
ActiveConnection:=cn
wksData.Cells(1,1).CopyFromRecordset Data:=rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
But if I have multiple criteria, such as:
Code:
strSQL = "SELECT * " & _
"FROM [DataSheet$] " & _
"WHERE [DataSheet$].[Fruit] =""orange"" AND [DataSheet$].[Fruit] = ""apple"""
would it be quicker to do a For Next Loop, as in:
Code:
Dim CriteriaArray() As Variant
CriteriaArray() = Array("orange", "apple")
Dim i As Integer
For i = LBound(CriteriaArray(), 1) To UBound(CriteriaArray(), 1)
strSQL = "SELECT * " & _
"FROM [Data$] " & _
"WHERE [Data$].[Segments] = """ & CriteriaArray(i) & """ "
rs.Open Source:=strSQL, _
ActiveConnection:=cn
wksData.Cells(1,1).CopyFromRecordset Data:=rs
rs.Close
Next i
Thanks