Reading data from excel table and outputting it to xml using vba macro?

Question

I have some data in an excel table in a format similar to the one below.

 Colum1_Heading Column2_Heading a 1 b 2 c 3

I am trying to convert the data in this table to XML, I will always have the same number of columns but an indeterminate number of rows.

The XML format is something like this

<?xml version="1.0" encoding="utf-8"?> <root> <tag1> <tag2> a - 1, b - 2, c - 3 </tag2> </tag1> </root>

So I think it should be fairly simple. So far I've started writing some code that creates a writeable string which I would then write to a new XML file, but I'm pretty new to VBA so I'm still researching a bunch on the internet. I've inputted the header and the beginning and end tags since those will always be the same. My question is how to read the rows of the table and write them to the XML in a format shown above. Any help would be appreciated. Please let me know if you need any additional info.

So if I'm understanding your XML schema, you just have one big text blob that has all the data in the worksheet in it? — Comintern
– Comintern, Commented Aug 23, 2016 at 21:01
@Mat'sMug I'm writing this code to convert an excel file into an XML to be read by an existing software. This is the XML schema that the software uses. I can't help that it doesn't make sense to you, it is what it is. — user2560035
– user2560035, Commented Aug 23, 2016 at 21:28
@Mat'sMug Actually I did make a mistake in my original post, there should be commas after every item but the last item. My apologies. — user2560035
– user2560035, Commented Aug 25, 2016 at 18:18

Mathieu Guindon · Accepted Answer · 2016-08-25 18:56:17Z

This should get you started. Add a reference to MSXML2:

Sub Test() With New MSXML2.DOMDocument60 Dim root As IXMLDOMNode Set root = .createElement("root") Dim tag1 As IXMLDOMNode Set tag1 = .createElement("tag1") Dim tag2 As IXMLDOMNode Set tag2 = .createElement("tag2") tag2.Text = ReadXmlContentFromWorksheet '"a - 1,b - 2,c - 3" tag1.appendChild tag2 root.appendChild tag1 .appendChild .createProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""") Set .DocumentElement = root '.Save "test.xml" Debug.Print .XML End With End Sub

Output:

<?xml version="1.0"?> <root><tag1><tag2>a - 1,b - 2,c - 3</tag2></tag1></root>

Note that the vertical whitespace is irrelevant

The ReadXmlContentFromWorksheet function would look something like this:

Private Function ReadXmlContentFromWorksheet() As String Dim result As String Dim lastRow As Long lastRow = MyDataSheet.Range("A" & MyDataSheet.Rows.Count).End(xlUp).Row Dim currentRow As Long For currentRow = 1 To lastRow result = result & MyDataSheet.Cells(currentRow, 1).Value & " - " _ & MyDataSheet.Cells(currentRow, 2).Value result = IIf(currentRow = lastRow, vbNullString, ",") Next ReadXmlContentFromWorksheet = result End Function

Collectives™ on Stack Overflow

Reading data from excel table and outputting it to xml using vba macro?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related