使用 VBA 保存为 XML？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9558013/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save as XML with VBA?
提问by Andrei Ion
Is there any way to save an Excel table as XML? I have the XML Schema file... and some data in a table... and I have in Excel the Save as XML file option but can I save a file as XML from VBA? I want to automate a process and I didn;t find nothing on this option. Thanks!
有没有办法将 Excel 表格保存为 XML？我有 XML 架构文件......以及表格中的一些数据......我在 Excel 中有“另存为 XML 文件”选项，但我可以从 VBA 将文件另存为 XML 吗？我想自动化一个过程，但我没有在这个选项上找到任何东西。谢谢！
采纳答案by Andrei Ion
the good ol' macro recorder saved me this time :)) (Why didn't I used it before I posted here?) So... To load an xml schema you have:
好的 ol' 宏记录器这次救了我 :)) （为什么我在这里发布之前没有使用它？）所以...要加载一个 xml 模式，你有：
ActiveWorkbook.XmlMaps.Add("Book2.xml", "raport").Name _
= "raport_Map"
And to save it as xml:
并将其保存为 xml：
ActiveWorkbook.SaveAsXMLData Filename:="Book3.xml", _
Map:=ActiveWorkbook.XmlMaps("raport_Map")
Who would have thought that it's that easy?
谁会想到这么容易？
回答by Solata
This link helped me the most -> http://curiousmind.jlion.com/exceltotextfile
这个链接对我帮助最大 - > http://curiousmind.jlion.com/exceltotextfile
Script on link:
链接上的脚本：
Sub MakeXML(iCaptionRow As Integer, iDataStartRow As Integer, sOutputFileName As String)
Dim Q As String
Q = Chr$(34)
Dim sXML As String
sXML = "<?xml version=" & Q & "1.0" & Q & " encoding=" & Q & "UTF-8" & Q & "?>"
sXML = sXML & "<rows>"
''--determine count of columns
Dim iColCount As Integer
iColCount = 1
While Trim$(Cells(iCaptionRow, iColCount)) > ""
iColCount = iColCount + 1
Wend
Dim iRow As Integer
iRow = iDataStartRow
While Cells(iRow, 1) > ""
sXML = sXML & "<row id=" & Q & iRow & Q & ">"
For icol = 1 To iColCount - 1
sXML = sXML & "<" & Trim$(Cells(iCaptionRow, icol)) & ">"
sXML = sXML & Trim$(Cells(iRow, icol))
sXML = sXML & "</" & Trim$(Cells(iCaptionRow, icol)) & ">"
Next
sXML = sXML & "</row>"
iRow = iRow + 1
Wend
sXML = sXML & "</rows>"
Dim nDestFile As Integer, sText As String
''Close any open text files
Close
''Get the number of the next free text file
nDestFile = FreeFile
''Write the entire file to sText
Open sOutputFileName For Output As #nDestFile
Print #nDestFile, sXML
Close
End Sub
Sub test()
MakeXML 1, 2, "C:\Users\jlynds\output2.xml"
End Sub
