Using DOM with ADO to Convert Excel Data to XML : Excel XML « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. Excel XML

Using DOM with ADO to Convert Excel Data to XML

 
Sub Convert_Excel_Data_to_XML()
     Dim oMyconnection As Connection
     Dim oMyrecordset As Recordset
     Dim oMyXML As DOMDocument
     Dim oMyWorkbook As String

     Set oMyconnection = New Connection
     Set oMyrecordset = New Recordset
     Set oMyXML = New DOMDocument

     oMyWorkbook = Application.ThisWorkbook.FullName

     oMyconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & oMyWorkbook & ";" & _
                      "Extended Properties=excel 8.0;" & _
                      "Persist Security Info=False"

     oMyrecordset.Open "Select * from [Sheet1$A1:D43]", oMyconnection, adOpenStatic
     oMyrecordset.save oMyXML, adPersistXML
     oMyXML.save (ThisWorkbook.Path & "\Output.xml")
         oMyrecordset.Close
         Set oMyconnection = Nothing
         Set oMyrecordset = Nothing
         Set oMyXML = Nothing

End Sub








Related examples in the same category

1.Get Schema
2.returns a workbook object with the XML data mapped to your spreadsheet:
3.Programmatically Changing XML Map Properties
4.Refresh Your XML Data
5.Creating Your Own XSD
6.Importing Data into an Existing XML Map
7.Exporting to an XML File
8.Leveraging DOM and XPath to Manipulate XML Files
9.Compatibility Mode
10.XML transform
11.Export to XML file