The following code belows creates a structured XML file from a excel spreadsheet. In this instance the spreadsheet is called CGT and has data starting in Cell B3 with headings starting in cell B2.
The code has some generic features in it , such as finding used columns and rows.
Another feature is, it uses the data in column B to create a XML Heading when the data changes in column B. It also uses the spreadsheet name as the main XML tag.
Below is a cut down version of the data being used
CGTHEADING,CGTDATE,CGTDESC,CGTCODE,CGTNOMINEE,CGTTYPE,CGTEVENT,CGTEVENTDESC,CGTQUANTITY,CGTAMOUNT Acquisitions,10/02/2005,Some Made Up Company ,NTFEEE,ANWM,BULK,BS,Purchase,1176.471113,"ú1,950.00" Acquisitions,10/02/2005,Another Made Up Co,OPEG,ANWM,BULK,BS,Purchase,783.568888,"ú3,900.00" Disposals,06/01/2005,Acme Made Up Company ,3244225,ANWM,INDIVIDUAL,LA,Lapse,-4258.84,ú0.00
The code which produces is the xml file from the spreadsheet is as follow s
Public Sub ExportToXML()
Dim colIndex As Integer Dim rwIndex As Integer Dim asCols() As String Dim oWorkSheet As Worksheet Dim sName As String Dim lCols As Long, lRows As Long Dim iFileNum As Integer Dim str_switch As String ' To use first column as node Dim blnSwitch As Boolean