创建用于将XML导出到某个文件夹的Excel宏 [英] Creating Excel Macro for Exporting XML to a certain folder

问题描述

我需要创建一个宏（我从来没有做过），如果你们可以指导我走一条正确的道路，那将是非常感激的。

我目前正在做的工作：
我已经创建了一个我已导入Excel的映射XML。一旦将其导入到Excel中，用户就会继续粘贴一些数据并将其导出以接收一个XML数据文件，然后用户可以将其删除到一个FTP工作，并将其导入到数据库中。 / p>

这是问题：
导出有以下节点，我不想要的：

 <？xml version =1.0encoding =UTF-8standalone =yes？> 
<根xmlns：xsi =http://www.w3.org/2001/XMLSchema-instance> 
  

相反，我想将其替换为以下内容：

 <？xml version =1.0？> 
<根xmlns =http://tempuri.org/CourseImport.xsd> 
  

如何自动化？在Excel中有什么样的设置可以使它发生吗？

基本上，我希望导出具有我的根，而不是默认的根，我想自动将文件删除到指定的路径：example：\development \school\course import

谢谢！

解决方案

我的同事实际上帮助了我。我以为我应该分享我所做的

  Sub ExportXML（）
'
'导出XML宏导出Excel到XML中的数据。 
'
 Const ForReading = 1 
 Const ForWriting = 2 
 
设置objFSO = CreateObject（Scripting.FileSystemObject）
 
'
 newFileName = Application.GetSaveAsFilename（out.xml，XML Files（* .xml），* .xmls）
如果newFileName = False然后
 Exit Sub 
 End If 
如果objFSO.FileExists（newFileName）然后
 objFSO.DeleteFile（newFileName）
结束If 
 ActiveWorkbook.XmlMaps（Root_Map）。导出URL：= newFileName 
 
 
设置objFile = objFSO.OpenTextFile（newFileName，ForReading）
 
 
 Dim count 
 count = 0 
 Do Until objFile.AtEndOfStream 
 strLine = objFile.ReadLine 
如果count = 0然后
 strNewContents = strNewContents& <？xml version =1.0？> &安培; vbCrLf 
 ElseIf count = 1然后
 strNewContents = strNewContents& < Root xmlns =http://tempuri.org/import.xsd> &安培; vbCrLf 
 Else 
 strNewContents = strNewContents& strLine& vbCrLf 
如果
 count = count + 1 
 
循环
 
 objFile.Close 
 
设置objFile = objFSO.OpenTextFile （newFileName，ForWriting）
 objFile.Write strNewContents 
 
 objFile.Close 
 End Sub 
  

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.

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?

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

Thanks!

解决方案

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

这篇关于创建用于将XML导出到某个文件夹的Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！