Filter data using SQL

[tiredofit]

Usually if I have data in an Access / SQL Server db (or even another Excel workbook), I can extract the data using ADO and SQL like:

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=" & "C:\MyData.xlsx" & ";" & _
             "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 what if my data is already within this workbook?

This does not work:

 strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & Thisworkbook.Worksheets("Data") & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"

(If I had Office 365, I would use the Filter function but alas, I don't).

Thanks

 

[Norie]

How does it not work?

 

[tiredofit]

How does it not work?

It crashes on this line:

 strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & ThisWorkbook.Worksheets("Data") & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"

with an error message:

Run-time error 438

Object does not support this property or method

 

[tiredofit]

It crashes on this line:

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & ThisWorkbook.Worksheets("Data") & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"

with an error message:

Run-time error 438

Object does not support this property or method

Sorted:

It should be:

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & ThisWorkbook.FullName & ";" & _
             "Extended Properties=""Excel 12.0 Macro;" & _
             "HDR=Yes;" & _
             "IMEX=1;" & _
             "MaxScanRows=0"";"