Need Help with Querying an Excel Table in VBA. ADO?

[brusk]

I have a workbook with dozens of tables that I'm currently using VBA to query and gather information and thought a SQL based query setup would be alot more efficient in gather the data I need for the worksheets. After searching I found ADO examples, is ADO the best/only solution? If so I could use a little guidance understanding some of this as it looks like ADO is used to query alot more than just excel so the examples I found differ quite a bit. I have Office 2010 Pro Plus. In the examples it looks like I need to add a reference to Microsoft ActiveX Data Objects Library. I would assume to pick the latest listed on my computer which is 6.1 but I have several 2.x versions listed. Also will this affect users that may still be running an older version of Excel.

I currently have this exampled plugged into a test sheet to see if it will at least run but fails at the rs.Open point so I figure something probably isn't correct on the Connection string info.

Public Function SQL() As Variant
    Dim wb As Workbook
    Dim cd As Worksheet
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strRangeAddress As String
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    
    Set wb = ThisWorkbook
    Set cd = wb.Sheets("ConfigurationData")
    
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    cn.Open strCon
    'strRangeAddress = Mid(ActiveWorkbook.Names.Item("cnfTableSystems").RefersToLocal, 2)
    strRangeAddress = cd.Name & cd.Range("cnfTableSystems").Address
    strSQL = "SELECT * FROM [" & strRangeAddress & "]"
    
    rs.Open strSQL, cn
    
    Debug.Print rs.GetString
    SQL = rs.GetString
End Function

 

[Worf]

I do not think the OP still needs this, but it may be useful for people googling the subject:

' Excel 2013
Public Sub SQL()
' add a reference to Microsoft ActiveX Data Objects 6.1 Library
Dim cn As ADODB.Connection, a, rs As ADODB.Recordset, strCon$
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
rs.Open "SELECT * FROM [sheet5$b2:c10]", cn
'MsgBox rs.GetString
a = rs.GetRows
MsgBox a(1, 5)                                  ' one array element
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub