excel vba create in-memory ADO recordset from table
'VBA function to create a detached, in-memory ADO recordset from an
'Excel table. This is the fastest method by far:
Function RSFromExcelTable(ws As Worksheet, table)
Dim sXML, oXML
With ws.ListObjects(table)
sXML = Union(.HeaderRowRange, .DataBodyRange).value(xlRangeValueMSPersistXML)
End With
Set oXML = CreateObject("MSXML2.DOMDocument")
oXML.LoadXML sXML
Set RSFromExcelTable = CreateObject("ADODB.Recordset")
RSFromExcelTable.Open oXML
End Function
'------------------------------------------------------------------------------------
MsgBox RSFromExcelTable(ActiveSheet, "Titanic").RecordCount