Malcolm Makin wrote:
I create an ADO Connection Object that is opens a
connection to the current Excel file

Not a good idea:

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)
http://support.microsoft.com/default...;en-us;Q319998

Try saving the relevant sheets to a temporary workbook, close it and
query the closed workbook e.g.

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"MySheet"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

' <<do something with recordset

rs.Close
Con.Close

End Sub