INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
VBA Visual Basic for Applications (Microsoft) FAQ
Excel How To
How Can i convert a excel spreadsheet to a XML File by Chance1234
Posted: 23 Mar 06
|
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
CODECGTHEADING,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 And here is how the XML file looks
CODE <?xml version="1.0" ?> - <CGT> - <RECORD> <CGTHEADING>Acquisitions</CGTHEADING> - <DATA> <CGTDATE>10/02/2005</CGTDATE> <CGTDESC>Some Made up CO</CGTDESC> <CGTCODE>NTFEEE</CGTCODE> <CGTNOMINEE>ANWM</CGTNOMINEE> <CGTTYPE>BULK</CGTTYPE> <CGTEVENT>BS</CGTEVENT> <CGTEVENTDESC>Purchase</CGTEVENTDESC> <CGTQUANTITY>1176.471113</CGTQUANTITY> <CGTAMOUNT>1950</CGTAMOUNT> </DATA> - <DATA> <CGTDATE>10/02/2005</CGTDATE> <CGTDESC>Another Madup Co</CGTDESC> <CGTCODE>OPEG</CGTCODE> <CGTNOMINEE>ANWM</CGTNOMINEE> <CGTTYPE>BULK</CGTTYPE> <CGTEVENT>BS</CGTEVENT> <CGTEVENTDESC>Purchase</CGTEVENTDESC> <CGTQUANTITY>783.568888</CGTQUANTITY> <CGTAMOUNT>3900</CGTAMOUNT> </DATA> </RECORD> - <RECORD> <CGTHEADING>Disposals</CGTHEADING> - <DATA> <CGTDATE>06/01/2005</CGTDATE> <CGTDESC>Acme Made up CO</CGTDESC> <CGTCODE>3244225</CGTCODE> <CGTNOMINEE>ANWM</CGTNOMINEE> <CGTTYPE>INDIVIDUAL</CGTTYPE> <CGTEVENT>LA</CGTEVENT> <CGTEVENTDESC>Lapse</CGTEVENTDESC> <CGTQUANTITY>-4258.84</CGTQUANTITY> <CGTAMOUNT>0</CGTAMOUNT> </DATA> </RECORD> </CGT>
The code which produces is the xml file from the spreadsheet is as follow s
CODEPublic 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
'--------Set WorkSheet and Columns and Rows
Set oWorkSheet = ThisWorkbook.Worksheets("CGT") sName = oWorkSheet.Name lCols = oWorkSheet.Columns.Count lRows = oWorkSheet.Rows.Count
ReDim asCols(lCols) As String
iFileNum = FreeFile Open "C:\temp\test2.xml" For Output As #iFileNum
'move through columms
For i = 1 To lCols - 1
If Trim(oWorkSheet.Cells(2, i + 1).Value) = "" Then Exit For asCols(i) = oWorkSheet.Cells(2, i + 1).Value Next i
If i = 0 Then GoTo ErrorHandler lCols = i
Print #iFileNum, "<?xml version=""1.0""?>" Print #iFileNum, "<" & sName & ">" ' add sheet name to xml file as First Node
'---------------------------------------------------------------- str_switch = "SDFSDKF" ' to trip loop For i = 3 To lRows
If Trim(oWorkSheet.Cells(i, 2).Value) = "" Then Exit For End If Debug.Print oWorkSheet.Cells(i, 2).Value If str_switch <> oWorkSheet.Cells(i, 2).Value Then If blnSwitch = True Then Print #iFileNum, "</" & "RECORD" & ">" End If Print #iFileNum, "<" & "RECORD" & ">" Print #iFileNum, " <" & asCols(1) & ">" & Trim(oWorkSheet.Cells(i, 2).Value) & "</" & asCols(1) & ">" blnSwitch = True Else End If Print #iFileNum, "<" & "DATA" & ">" For j = 3 To lCols Print #iFileNum, " <" & asCols(j - 1) & ">" & Trim(oWorkSheet.Cells(i, j).Value) & "</" & asCols(j - 1) & ">" Next j Print #iFileNum, "</" & "DATA" & ">" str_switch = oWorkSheet.Cells(i, 2).Value Next i
'------------End & close File -------------------- Print #iFileNum, "</" & "RECORD" & ">" Print #iFileNum, "</" & sName & ">"
Close #iFileNum
ErrorHandler: If iFileNum > 0 Then Close #iFileNum Exit Sub End Sub
|
Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum |
|
|
|