This weekend I was working on a spreadsheet and I needed to save the data as a recordset. Unfortunately there didn't seem to be a way to do it easily without VBA macros. So I wrote this macro to save the current worksheet as an ADODB.Recordset XML file. Remember to add a reference to Microsoft.ActiveX Data Object 2.8 Library in the Excel Visual Basic Editor.
Public Sub CreateAdoRecordsetXml() Dim rst As New ADODB.Recordset Dim sheet As Worksheet Dim col As Long, colcount As Long Dim row As Long, rowcount As Long Dim Filename As String Filename = ActiveWorkbook.Path + "\" + ActiveSheet.Name + ".xml" Set sheet = ActiveSheet colcount = sheet.UsedRange.Columns.count rowcount = sheet.UsedRange.Rows.count col = 1 row = 1 Do While col <= colcount Call rst.Fields.Append(sheet.Cells(row, col).Text, adVarChar, 255, adFldMayBeNull) col = col + 1 Loop rst.Open row = 2 Do While row <= rowcount col = 1 Call rst.AddNew With rst.Fields Do While col <= colcount If sheet.Cells(row, col).Text <> "" Then .Item(col - 1).Value = sheet.Cells(row, col).Text End If col = col + 1 Loop End With Call rst.Update row = row + 1 Loop Call rst.Save(Filename, adPersistXML) End Sub