今天需要将多个Excel文档转换为XML格式,本打算用MS Office自带的另存为XML文件的功能,结果转换成MS Office2003 XML之后的文件就是一坨屎!Office 2007自带的XML文档转换的功能也TMD超级繁琐,根据帮助手册自己建了.xsd文件导入到Excel之后也无法导出XML数据,白白浪费了时间。
后来Google到了这篇文章。文中提供了现成的VBA源代码,稍微修改一下即可拿来使用(中文注释为本人所加):
'Attribute VB_Name = "XL_to_XML"
Sub MakeXML()
' create an XML file from an Excel table
Dim MyRow As Integer, MyCol As Integer, Temp As String, YesNo As Variant, DefFolder As String
Dim XMLFileName As String, XMLRecSetName As String, MyLF As String, RTC1 As Integer
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String
MyLF = Chr(10) & Chr(13) ' a line feed command
DefFolder = "C:\MyXML\" 'change this to the location of saved XML files
YesNo = MsgBox("This procedure requires the following data:" & MyLF _
& "1 A filename for the XML file" & MyLF _
& "2 A groupname for an XML record" & MyLF _
& "3 A cellrange containing fieldnames (col titles)" & MyLF _
& "4 A cellrange containing the data table" & MyLF _
& "Are you ready to proceed?", vbQuestion + vbYesNo, "MakeXML CiM")
If YesNo = vbNo Then
Debug.Print "User aborted with 'No'"
Exit Sub
End If
XMLFileName = FillSpaces(InputBox("1. Enter the name of the XML fil