Working with VBA for transferring data from Excel sheets onto XML files, was Part-1 of my project concerning generation of apps for mobile database implementation.
For this purpose, I utilized the Developer tools of Microsoft Excel.
To enable the developer tools, you can follows the steps mentioned here.
Once, the developer tab appears on your screen, you can begin with the coding work!
My program code would work successfully with data in multiple TABs on the worksheet.
Now from the developer tab, you need to select the Command Button, under Insert option.
Command Button is inserted. You can now change its properties as follows -
Once the necessary changes are done, do ensure that the Design Mode button is selected, and then double click on the Button you earlier inserted to get into the VBA development area!
This is where my code comes into play.
Dim x As Integer
For x = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(x).Select
Dim strXML As String
If Application.Count(Selection) = 0 Then
FindUsedRange
End If
Set objXMLDoc = GenerateXMLDOM(Selection, "data")
Dim path As String
path = ThisWorkbook.path & "\" & ActiveSheet.Name & " (" & ThisWorkbook.Name & ")" & ".xml"
objXMLDoc.Save (path)
Next x
MsgBox ("Operation Complete!!!")
Paste this code inside the sub function of the command button.
Now you need to IMPORT the .bas file of the script that I have created.
This is where you need to MODIFY the code, according to the fields of your excel sheet and the required XML tags. For this you need to know basics of VBA coding.
In my case, when the Import is done, and the code runs without error, on clicking the Command Button earlier inserted, the required XML files are generated and stored in the same directory and a confirmation message is shown -
You can download my .bas file from here.
If you wish to view the complete SRS, you can download the pdf from here.
Mission Successful! :)
Original post: https://karanversation.blogspot.com/2012/01/excel2xml-experiments-with-vba.html