XLS to XML Macro for Mac Excel

Question

I need to convert a spreadsheet to a basic XML Format. The spreadsheet looks like this: enter image description here

I would like each row from the spreadsheet to look like this on the XML:

<item>
   <title>Directory Name</title>
   <address>Address, City, State</address>
   <phone>Phone</phone>
   <cell>Cell Phone</cell>
</item>

Any suggestions?

UPDATE: Per a few things I read, and per @Sancho.s suggested code I have used the following Macro:

Sub xls2xml()
    Dim rng1 As Range, rng2 As Range, cl As Range
    Set rng1 = Range("A2")
    Set rng1 = Range(rng1, rng1.End(xlDown))
    For Each cl In rng1
      Call dump_xml_line(cl)
    Next cl

End Sub

Sub dump_xml_line(cl As Range)
Dim n As Integer

n = FreeFile()
Open "C:\Users\Me\test.txt" For Output As #n


    Print #n, "<item>"
    Print #n, "   <title>" & cl.Text & "</title>"
    Print #n, "   <address>" & cl.Offset(0, 2).Text & "</address>"
    Print #n, "   <phone>" & cl.Offset(0, 1).Text & "</phone>"
    Print #n, "   <cell>" & cl.Offset(0, 3).Text & "</cell>"
    Print #n, "</item>"
    Close #n
End Sub

I had to use Print instead of Debug.Print because the Immediate Window isn't allowed to show more than 200 lines, and that is much much less than what the output of all this would be. Using this code, it creates the test.txt file, but only puts the very last row into the file, instead of all of them. What is wrong with it?

I have not tried anything, because I have no idea how to do this. I've never done anything like this. I have heard it can be done, but have no clue where to start, @sancho.s. That's why I asked for help. — user717452, Dec 10 '13 at 16:56
You are opening and writing the file with each call to `dump_xml_line`. Please see edited answer. — sancho.s ReinstateMonicaCellio, Dec 11 '13 at 10:49

score 4 · Accepted Answer · edited May 23 '17 at 12:29

This code should work for you. It is pretty much self-explanatory, and adaptation should be easy. To complement this code with other actions, you may find it helpful to record a macro, if you need to check what is the (approximate) code for a given task.

Sub xls2xml()
    Dim rng1 As Range, rng2 As Range, cl As Range
    Set rng1 = Range("A2")
    Set rng1 = Range(rng1, rng1.End(xlDown))
    For Each cl In rng1
      Call dump_xml_line(cl)
    Next cl
End Sub

Sub dump_xml_line(cl As Range)
    Debug.Print "<item>"
    Debug.Print "   <title>" & cl.Text & "</title>"
    Debug.Print "   <address>" & cl.Offset(0, 2).Text & "</address>"
    Debug.Print "   <phone>" & cl.Offset(0, 1).Text & "</phone>"
    Debug.Print "   <cell>" & cl.Offset(0, 3).Text & "</cell>"
    Debug.Print "</item>"
End Sub

Then you can copy the output from the immediate window.

Good luck.

EDIT To write to file instead of the immediate window, use the following (also self-explanatory) (see How can I write to a text file reliably from Excel VBA?)

Sub xls2xml_2()
    Dim rng1 As Range, rng2 As Range, cl As Range
    Set rng1 = Range("A2")
    Set rng1 = Range(rng1, rng1.End(xlDown))

    Dim strPath As String
    strPath = "test2.txt"
    Dim fnum As Integer
    fnum = FreeFile()
    Open strPath For Output As #fnum

    For Each cl In rng1
      Call write_xml_line_2(cl, fnum)
    Next cl

    Close #fnum
End Sub

Sub write_xml_line_2(cl As Range, fnum As Integer)
    Print #fnum, "<item>"
    Print #fnum, "   <title>" & cl.Text & "</title>"
    Print #fnum, "   <address>" & cl.Offset(0, 2).Text & "</address>"
    Print #fnum, "   <phone>" & cl.Offset(0, 1).Text & "</phone>"
    Print #fnum, "   <cell>" & cl.Offset(0, 3).Text & "</cell>"
    Print #fnum, "</item>"
End Sub

There is yet another option (see How to create and write to a txt file using VBA):

Sub xls2xml_1()
    Dim rng1 As Range, rng2 As Range, cl As Range
    Set rng1 = Range("A2")
    Set rng1 = Range(rng1, rng1.End(xlDown))

    Dim strPath As String
    strPath = "test1.txt"
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(strPath)

    For Each cl In rng1
      Call write_xml_line_1(cl, oFile)
    Next cl

    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing
End Sub

Sub write_xml_line_1(cl As Range, oFile As Object)
    oFile.WriteLine "<item>"
    oFile.WriteLine "   <title>" & cl.Text & "</title>"
    oFile.WriteLine "   <address>" & cl.Offset(0, 2).Text & "</address>"
    oFile.WriteLine "   <phone>" & cl.Offset(0, 1).Text & "</phone>"
    oFile.WriteLine "   <cell>" & cl.Offset(0, 3).Text & "</cell>"
    oFile.WriteLine "</item>"
End Sub

Ok, I feel really stupid asking this, but I'm having an issue. I open up the spreadsheet in Microsoft Excel 2008 for Mac. I click tools, macros. I see a box to name it and another box. The other box won't let me click in it, and everything but Cancel is greyed out. What's going on? — user717452, Dec 10 '13 at 19:41
@user717452 - You have to insert a module (Alt F11 to open VBA editor, right click on your project, insert, module), and paste the code in the new module. Then position the cursor anywhere inside `xls2xml` and run sub (F5). I have a PC, not MAC, but I guess it should be the same. — sancho.s ReinstateMonicaCellio, Dec 10 '13 at 20:10
I recommend something like [this](http://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htm). — sancho.s ReinstateMonicaCellio, Dec 10 '13 at 20:16
Ok, I moved it to a PC with 2010 running. I followed all your steps but when I click run, nothing happens. No other output window is visible, nothing @sancho.s — user717452, Dec 10 '13 at 20:26
@user717452 - The immediate window is not a new window that pops up. Look for it among existing windows, or check one of the links in Google "excel vba make immediate window visible". — sancho.s ReinstateMonicaCellio, Dec 10 '13 at 20:29
That showed it, but it only showed the end of the last 30 some odd rows in the immediate window and none from the beginning. @sancho.s — user717452, Dec 10 '13 at 20:40
it seems immediate window has 200 line maximum. I tried setting up a text file and running Print #n, instead of debug.print but it only saved one row — user717452, Dec 10 '13 at 21:12

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

If your objective is to get the converted text, and it is not mandatory for you to use VBA (you did not specify this to be the case), then you could export your worksheet as csv, and use another program (say, in Objective C) to carry out the conversion.

It will take you a few more clicks, but if you want to "upgrade" your worksheet or the manipulation you carry out to get your XML in the future, then you may feel more comfortable with this option, which is fully functional.

XLS to XML Macro for Mac Excel

2 Answers2