topic Exel Add-in create XML in Product Forum

Exel Add-in create XML

marwagamal — Thu, 08 Aug 2024 20:25:40 GMT

I want to automate process in which i fill Xlsm file then click on Add ins to create XML file to import it to a company system.

Can anyone help me to write code to do this.

Re: Exel Add-in create XML

Parthiban_Viatris24 — Fri, 09 Aug 2024 09:06:58 GMT

Excel VBA (Macro): Write a VBA script to automate the process of filling in the data within the XLSM file. You can create a macro that inputs the required data, possibly even pulling from another data source like a CSV file or a database.

Use the code below, making the necessary modifications according to your requirements as mentioned below.

Modify ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) to specify the range you want to export.
Adjust the Column1, Column2, Column3 elements to match the structure you need in the XML file.

Sub CreateXML()
Dim xmlDoc As Object
Dim rootElement As Object
Dim dataElement As Object
Dim cell As Range
Dim ws As Worksheet
Dim xmlFileName As String

' Create the XML Document object
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
xmlDoc.appendChild xmlDoc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")

' Create the root element
Set rootElement = xmlDoc.createElement("Data")
xmlDoc.appendChild rootElement

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Modify to your sheet name

' Loop through the rows and columns to add elements to the XML
For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
' Create an element for each row
Set dataElement = xmlDoc.createElement("Row")

' Add attributes or sub-elements as needed
dataElement.appendChild xmlDoc.createElement("Column1").appendChild xmlDoc.createTextNode(cell.Value)
dataElement.appendChild xmlDoc.createElement("Column2").appendChild xmlDoc.createTextNode(cell.Offset(0, 1).Value)
dataElement.appendChild xmlDoc.createElement("Column3").appendChild xmlDoc.createTextNode(cell.Offset(0, 2).Value)

' Append the row element to the root
rootElement.appendChild dataElement
Next cell

' Specify the XML file name and save the file
xmlFileName = ThisWorkbook.Path & "\output.xml" ' You can modify the path and file name

xmlDoc.Save xmlFileName

MsgBox "XML file created successfully: " & xmlFileName
End Sub

Re: Exel Add-in create XML

marwagamal — Fri, 09 Aug 2024 10:03:52 GMT

I already filled the xlsm i'm just pending at the step of Add ins creating Xml file

How can i use your code starting from the steps of creating xml file?

Please determine the inputs and outputs of the code

I'm new in blueprism🙏

Re: Exel Add-in create XML

Parthiban_Viatris24 — Fri, 09 Aug 2024 10:41:08 GMT

You can set up a sheet where you enter the path location and filename for the XML file. Use Blue Prism to update these cell values and then execute the macro. The macro will read the values from the cells and use them to create the XML file.

Use MS Excel VBO object to access the file, update the cell and run macro for the same

Re: Exel Add-in create XML

marwagamal — Fri, 09 Aug 2024 14:30:08 GMT

thanks alot it worked