vba 创建用于将 XML 导出到特定文件夹的 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15142523/
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
Creating Excel Macro for Exporting XML to a certain folder
提问by NonProgrammer
I need to create a macro (which I have never done before) and if you guys can guide me to a right path, it would be really appreciated.
我需要创建一个宏（我以前从未做过），如果你们能指导我走上正确的道路，我将不胜感激。
What I'm doing currently: I have created a mapping XML which I have imported into Excel. Once it is imported into Excel, users will then go ahead and paste some data in it and export it to receive an XML data file, which then user can drop it to a FTP where the job picks it up and imports it into database.
我目前在做什么：我创建了一个映射 XML，并将其导入 Excel。一旦它被导入到 Excel 中，用户将继续在其中粘贴一些数据并将其导出以接收 XML 数据文件，然后用户可以将其拖放到 FTP 中，在那里作业将其提取并导入到数据库中。
Here's the problem: The export has following node, which I do not want:
问题是：导出有以下节点，我不想要：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
Instead I want to replace it with following:
相反，我想用以下内容替换它：
<?xml version="1.0" ?>
<Root xmlns="http://tempuri.org/CourseImport.xsd">
How do I automate this? Is there some kind of setting in Excel that could make it happen?
我如何自动执行此操作？Excel 中是否有某种设置可以实现？
Basically, I want the export to have my root instead of the default root and I want to automatically be able to drop the file to specified path: example: \development\school\course import
基本上，我希望导出具有我的根而不是默认根，并且我希望能够自动将文件拖放到指定路径：例如：\development\school\course import
Thanks!
谢谢！
采纳答案by NonProgrammer
My co-worker actually helped me out with this. I thought I should share what I did
我的同事实际上帮我解决了这个问题。我想我应该分享我所做的
Sub ExportXML()
'
' Export XML Macro exports the data that is in Excel to XML.
'
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
'
newFileName = Application.GetSaveAsFilename("out.xml", "XML Files (*.xml), *.xmls")
If newFileName = False Then
Exit Sub
End If
If objFSO.FileExists(newFileName) Then
objFSO.DeleteFile (newFileName)
End If
ActiveWorkbook.XmlMaps("Root_Map").Export URL:=newFileName
Set objFile = objFSO.OpenTextFile(newFileName, ForReading)
Dim count
count = 0
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If count = 0 Then
strNewContents = strNewContents & "<?xml version=""1.0"" ?>" & vbCrLf
ElseIf count = 1 Then
strNewContents = strNewContents & "<Root xmlns=""http://tempuri.org/import.xsd"">" & vbCrLf
Else
strNewContents = strNewContents & strLine & vbCrLf
End If
count = count + 1
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(newFileName, ForWriting)
objFile.Write strNewContents
objFile.Close
End Sub
