If you are here for a sharepoint tip, disregard this post- I am posting this to myself so I will not lose this code. Basically, it is an excel macro that genenrates an xml file from the current sheet. If you do want to use it, add a reference to microsoft xml in the VBA environment, and paste the code below to a module.
There are many way to improve it, but I just wrote this to quickly achieve a small task that I needed done.
Sub makeXml() ActiveCell.SpecialCells(xlLastCell).Select Dim lastRow, lastCol As Long lastRow = ActiveCell.Row lastCol = ActiveCell.Column Dim iRow, iCol As Long Dim xDoc As New DOMDocument Dim rootNode As IXMLDOMNode Set rootNode = xDoc.createElement("Root") Dim rowNode As IXMLDOMNode Dim colNode As IXMLDOMNode 'loop over the rows For iRow = 2 To lastRow Set rowNode = xDoc.createElement("Row") 'loop over the columns For iCol = 1 To lastCol If (Len(ActiveSheet.Cells(1, iCol).Text) > 0) Then Set colNode = xDoc.createElement(GetXmlSafeColumnName(ActiveSheet.Cells(1, iCol).Text)) colNode.Text = ActiveSheet.Cells(iRow, iCol).Text rowNode.appendChild colNode End If Next iCol rootNode.appendChild rowNode Next iRow xDoc.appendChild rootNode xDoc.Save ("c:\temp\temp.xml") set xDoc = Nothing End Sub Function GetXmlSafeColumnName(name As String) Dim ret As String ret = name ret = Replace(ret, " ", "_") ret = Replace(ret, ".", "") ret = Replace(ret, ",", "") ret = Replace(ret, "&", "") ret = Replace(ret, "!", "") ret = Replace(ret, "@", "") ret = Replace(ret, "$", "") ret = Replace(ret, "#", "") ret = Replace(ret, "%", "") ret = Replace(ret, "^", "") ret = Replace(ret, "*", "") ret = Replace(ret, "(", "") ret = Replace(ret, ")", "") ret = Replace(ret, "-", "") ret = Replace(ret, "+", "") GetXmlSafeColumnName = ret End Function
No comments:
Post a Comment