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

[@richard.marseglia]

I have tried this many times I created my own XML Source File, named the Columns the same as the Source Fields, when I attempt to Map the Sources to the Header (A1), it only selects A1 and not the data in A2, A3, etc... Same for all other columns. So when I export, it's only exporting out only the Row A data, none of the additional rows that have data in them. I can't figure out what I'm doing wrong. Thank you

[@exceldemy2006]

Hello @mirghtaed, You are most welcome. It seems like the issue is related to how you're mapping the XML fields. Make sure you're selecting the entire range of cells (not just A1) for the mapping. XML mapping links the data structure to Excel, so if it's only selecting the header, it won’t map the entire range. Double-check that the XML schema matches your data structure and that your range selection is correct for the rows. Try remapping by dragging the field onto the entire column, not just the header cell. 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

[@rokhalelalit69]

How can third party exe can link with excel and update the data and fetch back to exe with new data

[@exceldemy2006]

Hello @rokhalelalit69, To link a third-party EXE with Excel you can use Power Query or CSV files. 1. The EXE can write data into a CSV or Excel file. 2. You can use Power Query to automatically fetch and update the data from the CSV/Excel file. 3. Modify the data as needed. The EXE reads back the updated data from the same file. Regards ExcelDemy

[@AmrYax]

When I link the column header, it only highlights the header not the values below it. Then when I export it only shows the headers in the XML. Not sure what I am doing wrong

[@exceldemy2006]

Hello @AmrYax, Such types of issues occur due to incomplete mapping. Ensure that you have mapped not just the headers but also the data cells below them in the XML Source panel. If only the header is highlighted, it means the data isn’t properly linked to the XML schema. Also, check if the imported HTML file is well-structured, as improper HTML could cause mapping issues. Try re-mapping and see if it resolves the problem. If you want you can share your HTML schema to verify. Regards ExcelDemy

[@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.

[@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

[@rokhalelalit69]

How to update data in Excel and update in link XML file

[@exceldemy2006]

Hello @rokhalelalit69, To update data in Excel and sync it with a linked XML file, you can follow these steps: First, create an XML mapping in Excel by importing the XML schema and assigning it to the cells. Next, Modify the data in the mapped cells as needed. Once the changes are made, export the updated data back to the XML file using the "Export" option in the Developer tab. This ensures that the Excel data is reflected in the linked XML file. Regards ExcelDemy