How to Add XML Source File Names to Imported Excel Records
Question details
The user needs a way to automatically append the original XML source file name next to the corresponding records when importing multiple XML files into an Excel spreadsheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing multiple XML files into a single Excel table while tracking the origin of each data row.
- Observed behavior
- By default, imported XML records do not natively display the name of the source XML file they originated from, making data tracking difficult without custom automation.
Ensure that the Developer tab is enabled in your Excel ribbon so you can access the VBA editor and XML Map Properties. It is highly recommended to back up your existing workbook before running new macros.
Use VBA Macro and XML Map Properties
Configure XML Map Properties to append data, then use a custom VBA macro to loop through files and record their source names.
To accomplish this task efficiently, you need to combine Excel's XML mapping features with a VBA macro. The macro automates the file importing process while explicitly writing the file name into an adjacent column.
Navigate to the Developer tab, click 'Source' in the XML group to open the XML Source pane. Click 'XML Maps...', select your map, and click 'XML Map Properties'. Check the box for 'Append new data to existing XML tables' and click OK.
Press Alt + F11 to launch the Visual Basic for Applications (VBA) editor.
Go to Insert > Module and create a new VBA script. The script should use the Dir() function to loop through your designated folder of XML files and use ActiveWorkbook.XmlMaps("YourMap").Import to bring in the data.
Within your VBA loop, calculate the last row of the newly imported data and instruct VBA to write the current XML file name (stored in your Dir() variable) into the designated column next to those new records.
Press F5 or go to Run > Run Sub/UserForm. Excel will process each XML file, map the required fields, and log the source file name beside each corresponding entry.
Manage Data Efficiently with WPS Spreadsheet
WPS Office provides robust support for advanced data handling and macros, allowing you to automate repetitive data tracking tasks and import routines easily.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and ensure your macro support feature is enabled.
- 2. Access the Developer Tools: Go to the Developer tab to access the script editor and manage your data macros.
- 3. Run Data Import Scripts: Create or paste your data import macro to automatically process multiple files and append file names.
Frequently Asked Questions
Can I extract the source file name without using a VBA macro?
Yes, native Excel features like Power Query can extract the 'Source.Name' column automatically when using the 'Get Data from Folder' option. However, if your workflow relies strictly on XML Maps mapped to a specific schema, a VBA macro remains the most precise method.
Where do I find the XML Map Properties dialog?
Go to the Developer tab, click on 'Source' in the XML group to open the XML Source task pane. Click the 'XML Maps...' button at the bottom of the pane, select your active map from the list, and then click 'Properties'.
Why is the Developer tab missing from my Excel ribbon?
The Developer tab is hidden by default to keep the interface clean. To enable it, go to File > Options > Customize Ribbon, and check the box next to 'Developer' in the right-hand main tabs list.
What happens if I don't check 'Append new data to existing XML table'?
If this option is left unchecked, Excel will overwrite the existing data in the mapped XML table every time a new XML file is imported by the macro, meaning you will only see the records from the very last file processed.
