How To Have Excel VBA Write To XML File

This post is called “How To Have Excel VBA Write To XML File”, and is going to be an enhancement to my previous post:

http://www.vbastring.com/blog/how-to-create-an-excel-userform-to-add-edit-and-delete/

In this post we are going to add a button to our form where we can save the current contents of the worksheet to
an XML file.

Here is the code for the new button. We are creating a text file and then writing the contents of each column to each tag:

Private Sub btnXML_Click()
    Dim lngRow As Long
   
    strpath = ActiveWorkbook.Path & "\ContactList.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim objFile As Object
    Set objFile = fso.CreateTextFile(strpath, True, True)
    
    objFile.Write "<?xml version='1.0'?>" & vbCrLf
    objFile.Write "<ContactList>" & vbCrLf
    
    For lngRow = 2 To 2000
        If Range("A" & lngRow) <> "" Then

            objFile.Write "<CustomerID>" & Range("A" & lngRow) & "</CustomerID>" & vbCrLf
            objFile.Write "<CompanyName>" & Range("B" & lngRow) & "</CompanyName>" & vbCrLf
            objFile.Write "<ContactName>" & Range("C" & lngRow) & "</ContactName>" & vbCrLf
            objFile.Write "<ContactTitle>" & Range("D" & lngRow) & "</ContactTitle>" & vbCrLf
            objFile.Write "<Address>" & Range("E" & lngRow) & "</Address>" & vbCrLf
            
        End If
        
    Next
    
    objFile.Write "</ContactList>" & vbCrLf & vbCrLf
    
End Sub


When we open ContactList.xml in a XML Editor or the Microsoft Edge browser (because it renders XML good, and should show it, if you have done it correctly) we should see the file contents looking like this:

Now if we need to send this to someone to load into their database or otherwise, our data is structured.
Let me know if you have any questions
****************************************************