Generate XML Files from Excel Rows with a VBA Macro
Question details
Generate one XML file per Excel data row using a VBA macro, with domain and agent values from columns G and H.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains rows of data that need to be exported as separate XML files with repeated command elements.
- Observed behavior
- Each populated row should produce an XML file containing its domain and agent values.
The example uses Excel for Windows and ADODB.Stream to write UTF-8 files. Create an output folder, confirm that the data sheet has headers in row 1 and values in columns G and H, and save the workbook as an XLSM file.
Run a VBA macro to export each Excel row as XML
Use this example when you need one XML file per row and can adapt the sample XML structure to your required format.
The sample creates two command elements per row: one contains the domain and the other contains the agent. Change the tags in MakeXML if your target application requires a different XML structure.
File names use worksheet row numbers, such as row_2.xml. Existing files with the same names in the output folder will be overwritten.
On a worksheet named Sheet1, enter the existing output folder path in B1, such as C:\XMLExport, and the data worksheet name in B2. The data sheet can be Sheet1 or another sheet.
Press Alt+F11, choose Insert > Module, and paste this code into the module: Option Explicit Sub ExportRowsToXML() Dim config As Worksheet, dataSheet As Worksheet Dim folder As String, sheetName As String Dim lastRow As Long, r As Long Dim domain As String, agent As String Set config = ThisWorkbook.Worksheets("Sheet1") folder = Trim$(CStr(config.Range("B1").Value)) sheetName = Trim$(CStr(config.Range("B2").Value)) If folder = "" Or sheetName = "" Then MsgBox "Enter an output folder in B1 and a data sheet name in B2." Exit Sub End If If Dir(folder, vbDirectory) = "" Then MsgBox "The output folder does not exist: " & folder Exit Sub End If If Right$(folder, 1) <> "\" Then folder = folder & "\" Set dataSheet = ThisWorkbook.Worksheets(sheetName) lastRow = Application.Max(dataSheet.Cells(dataSheet.Rows.Count, "G").End(xlUp).Row, _ dataSheet.Cells(dataSheet.Rows.Count, "H").End(xlUp).Row) For r = 2 To lastRow domain = CStr(dataSheet.Cells(r, "G").Value) agent = CStr(dataSheet.Cells(r, "H").Value) If domain <> "" Or agent <> "" Then MakeXML folder & "row_" & r & ".xml", domain, agent End If Next r MsgBox "Export complete. Check: " & folder End Sub Private Sub MakeXML(ByVal filePath As String, ByVal domain As String, ByVal agent As String) Dim xml As String, stream As Object xml = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & _ "<commands>" & vbCrLf & _ " <command><domain>" & XmlEscape(domain) & "</domain></command>" & vbCrLf & _ " <command><agent>" & XmlEscape(agent) & "</agent></command>" & vbCrLf & _ "</commands>" Set stream = CreateObject("ADODB.Stream") stream.Type = 2 stream.Charset = "utf-8" stream.Open stream.WriteText xml stream.SaveToFile filePath, 2 stream.Close End Sub Private Function XmlEscape(ByVal value As String) As String value = Replace(value, "&", "&") value = Replace(value, "<", "<") value = Replace(value, ">", ">") value = Replace(value, Chr$(34), """) value = Replace(value, "'", "'") XmlEscape = value End Function
In Excel, press Alt+F8, select ExportRowsToXML, and click Run. Open the output folder and inspect a generated XML file to confirm its tags and values match the receiving application's requirements.

Review Excel Source Data in WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files and review source data before moving between office suites; the Excel VBA export itself must be run in a compatible macro environment.

Frequently Asked Questions
Where do I specify the XML output folder?
Enter the path to an existing folder in cell B1 on Sheet1. The macro writes files such as row_2.xml into that folder.
How do I change the columns used for domain and agent?
In the VBA module, change the G and H column references in both the lastRow calculation and the Cells(r, ...) assignments.
What if the XML tags need a different structure?
Edit the XML string in the MakeXML procedure to match the receiving application's required tags and nesting. Keep XmlEscape around cell values inserted into XML text.
Why was no XML file created?
Check that Sheet1 cells B1 and B2 contain an existing folder and the exact data sheet name. Also check that at least one cell in columns G or H below row 1 contains data, then run ExportRowsToXML from Alt+F8.
Does the macro create a file when only one value is present?
Yes. A row is exported if either column G or H has a value; the missing value appears as an empty domain or agent element.




