Nimble Coder

Adventures in Nimble Coding

posts - 77, comments - 56, trackbacks - 1

My Links

Post Categories

Blog roll

<< Welcome | Home | C# Utility to export Excel Worksheet As ADO Recordset XML >>

Export Excel Worksheet to ADO Recordset

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

Print | posted on Monday, January 15, 2007 12:22 PM | Filed Under [ VB Excel ]

Feedback

No comments posted yet.

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 5 and 5 and type the answer here: Enter the code shown above: