How to Create XML Mapping in Excel

[@TolulopeObitayo]

Please how do I create my XML file link to notepad. I can't find the link to the description you mentioned in your video?

[@exceldemy2006]

Hello @TolulopeObitayo, Please check out the description box. XML code is given. I am attaching the XML here again: www.dropbox.com/scl/fi/rb4zp98b8byc2n5nmhqix/XML-Code.txt?rlkey=16394qihlfu2m56fy3zpo1xcl&st=yhzteyqn&dl=1 XML Code: 1 1 1 1 1 1 1 1 Regards ExcelDemy

[@dennisarruda4866]

this is just not creating an xml file for me. i only get text from the notepad

[@exceldemy2006]

Hello @dennisarruda4866, Before exporting you need to import the xml file first to map it correctly. Please follow the steps of the video or the given article carefully : www.exceldemy.com/create-xml-mapping-in-excel/ N.B: While saving the exported XML file follow the steps: Developer tab >> Export >> select "Save as type: XML Files (*.xml)" >> Export For clarity, I again exported the XML file, and it is exported correctly. But, if you have a problem with the exported file, you can choose the opening option. Right-click on the XML file >> then select Open with >> choose any option. Here is the exported file: www.dropbox.com/scl/fi/p4trbksnnw6fk53gjquss/Exporting-xml-file.xml?rlkey=0uledanibo7yd6oy34g8xxezx&st=530l21mp&dl=1 Regards ExcelDemy

[@SuperVikas45]

Can I generate seperate xml files for each data

[@exceldemy2006]

Dear, Thanks for your query! The answer is YES. You generate separate XML files for each data set. To do so, we have developed an Excel VBA Sub-procedure to generate an XML file for each cell in UsedRange. You can download the workbook used to solve your problem: www.exceldemy.com/wp-content/uploads/2024/06/Vikas-SOLVED.xlsm Excel VBA Sub-procedure: Sub WorkingOnXML() Dim ws As Worksheet Dim lastRow, lastColumn As Long Dim xmlData, xmlLine As String Dim fileName As String Dim xmlFile As Integer Dim dataRow As Long, col As Long Dim cell As Range Dim xmlCounter As Long Set ws = ThisWorkbook.Sheets("Sheet1") Application.DisplayAlerts = False xmlCounter = 1 For Each cell In ws.usedRange xmlData = "" xmlData = xmlData & "" xmlLine = "" xmlLine = xmlLine & "" & cell.Value & "" xmlLine = xmlLine & "" xmlData = xmlData & xmlLine xmlData = xmlData & "" fileName = ThisWorkbook.Path & "\XML" & xmlCounter & ".xml" On Error Resume Next Kill fileName On Error GoTo 0 xmlFile = FreeFile Open fileName For Output As xmlFile Print #xmlFile, xmlData Close xmlFile xmlCounter = xmlCounter + 1 Next cell Application.DisplayAlerts = True End Sub

[@Mantades]

Is it possible to edit xml files in excel, add columns in the spreadsheet and then somehow add them to the XML Source window? (so without the first step, because I just want to edit an existing structure and data, not create a new one)

[@exceldemy2006]

Dear, Thanks for your query. You can not edit XML files within Excel. You can use an XML editor to edit an XML file. If you ever try to avoid the editing step and want to create an XML structure based on the worksheet data, I present a VBA sub-procedure that creates an XML file based on the worksheet data without any columns or row dependencies. Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Creating-XML-file-structure-based-on-worksheet-data.gif You can download the workbook: www.exceldemy.com/wp-content/uploads/2024/04/Creating-XML-Mapping.xlsm Excel VBA Sub-procedure: Sub WorkingOnXML() Dim ws As Worksheet Dim lastRow, lastColumn As Long Dim xmlData, xmlDataNext As String Dim fileName As String Dim xmlFile As Integer Dim i, j As Long Dim xmlMap As xmlMap Dim clearRange As Range Set ws = ThisWorkbook.Sheets("Sheet1") lastColumn = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row Application.DisplayAlerts = False xmlData = "" xmlDataNext = "" xmlData = xmlData & xmlDataNext For i = 5 To lastRow xmlData = xmlData & "" For j = 2 To lastColumn xmlData = xmlData & "" xmlData = xmlData & ws.Cells(i, j).Value & "" Next j xmlData = xmlData & "" Next i xmlData = xmlData & "" fileName = ThisWorkbook.Path & "\XMLFile.xml" If Dir(fileName) "" Then Kill fileName End If xmlFile = FreeFile Open fileName For Output As xmlFile Print #xmlFile, xmlData Close xmlFile Set clearRange = ws.Range("B4", ws.Cells(lastRow, lastColumn)) clearRange.ClearContents For Each xmlMap In ThisWorkbook.XmlMaps xmlMap.Delete Next xmlMap ThisWorkbook.XmlImport Url:=ThisWorkbook.Path & "\XMLFile.xml", _ ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$4") Application.DisplayAlerts = True End Sub

[@Mantades]

@@exceldemy2006 Thanks for the VBA code, but I already found a suitable solution to my problem (also using your video). Since I couldn't add anything to the map file from the Excel, then I decided to make a file of my own (step 1 in the video), that included my new column, and just replaced the mapping file that Excel generates on it's own. After that it was simple to write what I want in the new column and then export it to a new .xml file. In my case normal xml editors are a bad choice, because I do translations, so I need a column-like structure (like the one Excel does), not an editing tool which usually presents data in a code-like way.

[@exceldemy2006]

@@Mantades Dear, You are welcome! Thanks for letting us know you have overcome your situation. That's great! We are glad you found a solution using the video. It is great to see how you have fitted your workflow by adding new columns and replacing the mapping.