excel export range recordset to closed worksheet code example
Example: excel vba create ADO recordset from table
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