VBA & Macro Problems

Generate XML Files from Excel Rows with a VBA Macro

Algirdas JasaitisAlgirdas Jasaitis Nov 22, 2026 868 views

Question details

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

Generate XML Files from Excel Rows with a VBA Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the configuration

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.

2
Add the macro

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, "&", "&amp;") value = Replace(value, "<", "&lt;") value = Replace(value, ">", "&gt;") value = Replace(value, Chr$(34), "&quot;") value = Replace(value, "'", "&apos;") XmlEscape = value End Function

3
Run and check the export

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.

Run a VBA macro to export each Excel row as XML
Check the required XML format: This example escapes common XML special characters but does not remove invalid XML control characters. Clean such characters from the source cells if necessary.
Free Microsoft Office alternative

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.

Review domain and agent values in spreadsheet columns before XML export.Open and edit compatible Microsoft Excel XLSX and XLS files.Use a familiar interface for a smoother transition from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.