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, Feb 02 '13 at 06:26
for Excel 2010 you probably need ADO 2.6, 2.6 or higher: http://social.msdn.microsoft.com/Forums/en/exceldev/thread/2a4ca815-1fba-4c12-894b-605264df180e, *AND* you need to reference MSXML: http://social.msdn.microsoft.com/Forums/en/isvvba/thread/b6f8d92a-d39c-48f5-b6c4-9e9ed155eb6f — paulsm4, Feb 02 '13 at 06: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, Feb 02 '13 at 06:49
Perhaps this might help: http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102064019990&lcid=1033&NS=EXCEL&Version=12&pid=CH010064852&CTT=4 — Peter L., Feb 02 '13 at 08:27
No i want it has to be done through vba code, which i use in a button click. thanks for replr.. — SaiKiran Mandhala, Feb 02 '13 at 09:52

score 1 · Answer 1 · answered Jun 27 '13 at 14:27

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 '13 at 14:27

Tony Karel

176
1
10

score 0 · Answer 2 · edited May 23 '17 at 12:18

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 '17 at 12:18

Community

1
1

answered Feb 02 '13 at 06:26

paulsm4

114,292
17
138
190

Vba-Excel: Export Excel Sheet to XML?

2 Answers2