CODEWITHSUNDEEP
xmlexcelvba
SaiKiran Mandhala
SaiKiran Mandhala

Reputation: 365

Vba-Excel: Export Excel Sheet to XML?

I have requirement to export excel spreadsheet data to XML. To do this i added few lines of code in WorkSheet_Activate i.e.,

Private Sub Worksheet_Activate()

 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:C100]", oMyconnection, adOpenStatic

 oMyrecordset.Save oMyXML, adPersistXML

 oMyXML.Save (ThisWorkbook.Path & "\Output.xml")

 oMyrecordset.Close
Set oMyconnection = Nothing
Set oMyrecordset = Nothing
Set oMyXML = Nothing

But when ever i try to execute it i am getting an error like User-Defined Datatype not found. Actually i am getting this error because of the line Dim oMyXML As DOMDocument. Am i missing any reference? Any help would be appreciated greatly.

Upvotes: 0

Views: 1710

Answers (2)

Tony Karel
Tony Karel

Reputation: 176

I know this thread is a few months old, but you'll need to add a reference to "Microsoft XML, v6.0" (or whatever version you have/need) to use the DOMDocument datatype.

Dim oMyXML As MSXML.DOMDocument
Set oMyXML = New MSXML.DOMDocument

Upvotes: 1

paulsm4
paulsm4

Reputation: 121849

he problem isn't "XML output" per se, but weirdness with Microsoft VBA and referencing the right ActiveX components.

Look at either/both of these links and see if they help:

Upvotes: 0

Related Questions