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
****************************************************
|