Using Excel's XML maps can be tricky and you need the maps to use the
Save As XML option. I find it simpler to just write the XML data
directly. The following is some code I wrote a while ago. To use it,
select the data that you want to export as XML and then run the code.
It will prompt you for an output file name. The code assumes that the
first row of the selection is column headers to be used as element
names in the XML. Change the lines marked with <<< to the correct
values. RootName is the name of the root elemnt of the XML document.
RowName is the element name for each row of data. The result is a very
simple XML file, with attributes, namespace declarations, or schema
references. You could easily modifty the code to add these features.
Sub QDXML()
Dim FName As Variant
Dim FNum As Integer
Dim Root As Range
Dim RootName As String
Dim RowName As String
Dim RNdx As Long
Dim CNdx As Long
FName = Application.GetSaveAsFilename(filefilter:="XML Files,*.xml")
If FName = False Then
Exit Sub
End If
Set Root = Selection.Cells(1, 1)
RootName = "DocElement" '<<< name of root XML element
RowName = "Item" '<<< row element name
FNum = FreeFile
Open FName For Output Access Write As #FNum
rint #FNum, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #FNum, "<" & RootName & ">"
For RNdx = 1 To Selection.Rows.Count
Print #FNum, Space(4) & "<" & RowName & ">"
For CNdx = 1 To Selection.Columns.Count
Print #FNum, Space(8) & _
"<" & Selection.Cells(1, CNdx) & ">" & _
Selection.Cells(RNdx, CNdx) & "</" & _
Selection.Cells(1, CNdx) & ">"
Next CNdx
Print #FNum, Space(4) & "</" & RowName & ">"
Next RNdx
Print #FNum, "</" & RootName & ">"
Close #FNum
End Sub
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com