Vba-Excel: Export Excel Sheet to XML?

Question

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.

I have already added the reference Microsoft ActiveX Data Objects 2.5 Library. but getting same error. I am using Excel2010. — SaiKiran Mandhala
– SaiKiran Mandhala, Commented Feb 2, 2013 at 6:26
for Excel 2010 you probably need ADO 2.6, 2.6 or higher: social.msdn.microsoft.com/Forums/en/exceldev/thread/…, AND you need to reference MSXML: social.msdn.microsoft.com/Forums/en/isvvba/thread/… — paulsm4
– paulsm4, Commented Feb 2, 2013 at 6:31
Yeah i added that reference also, I have added Microsoft ActiveX Data Objects 2.5 Library, and when i try to add DAO 3.6 reference it giving me an error like error in loading dll. — SaiKiran Mandhala
– SaiKiran Mandhala, Commented Feb 2, 2013 at 6:49
No i want it has to be done through vba code, which i use in a button click. thanks for replr.. — SaiKiran Mandhala
– SaiKiran Mandhala, Commented Feb 2, 2013 at 9:52

Tony Karel · Accepted Answer · 2013-06-27 14:27:40Z

1

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

answered Jun 27, 2013 at 14:27

Tony Karel

1761 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:57Z

0

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:

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Feb 2, 2013 at 6:26

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Collectives™ on Stack Overflow

Vba-Excel: Export Excel Sheet to XML?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related