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
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 & ""
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
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)
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
@@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.
@@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.
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
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
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
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
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
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?
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
Can I generate seperate xml files for each data
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
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)
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
@@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.
@@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.
How can third party exe can link with excel and update the data and fetch back to exe with new data
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
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
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
this is just not creating an xml file for me. i only get text from the notepad
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
How to update data in Excel and update in link XML file
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