vba create in-memory ADO recordset from table code example

Example: 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