如何利用vba将excel转换成xml格式的文件

Question

非IT的理工女，求大神指导！想将图片1中的excel数据转换为图片2中的xml格式，不知道应怎样编写VBA程序，目前已经写出一部分，但是转换出来的xml数据不全，求帮忙给出程序或将下面程序补全：
Sub cheliang()
Dim fso As Object, sFile As Object
Dim iRow As Integer, FileName As String
Set fso = CreateObject("scripting.FileSystemObject")
FileName = "D:\EV.xml"
Set sFile = fso.CreateTextFile(FileName)
For iRow = 2 To Sheet1.Range("A65536").End(xlUp).Row
sFile.write "<parameter" & "" & "name=" & """" & Sheet1.Cells(iRow, 1).Value & """" & ""
sFile.write "displayname=" & """" & Sheet1.Cells(iRow, 2).Value & """" & ""
sFile.write "unit=" & """" & Sheet1.Cells(iRow, 3).Value & """" & ""
sFile.write "type=" & """" & Sheet1.Cells(iRow, 4).Value & """" & ""

Next iRow

'
' cheliang Macro
'
End Sub

Answer 1

Rem 需要引用 Microsoft xml v6.0
Rem 生成xml文件，参数为Sheet
Sub GenerateXml(sht As Worksheet)
sht.Activate
Dim xmlDoc As DOMDocument
Dim Description As IXMLDOMElement
Dim instruction As IXMLDOMElement
Dim query As IXMLDOMElement
Dim colum As IXMLDOMElement
Dim segment As IXMLDOMElement
Dim segmentCol(100) As IXMLDOMElement
Dim segmentRow(100) As IXMLDOMElement
Dim dimension(1000) As IXMLDOMElement
Dim member As IXMLDOMElement
Dim rows As IXMLDOMElement
Dim pages As IXMLDOMElement
Dim POV As IXMLDOMElement
Dim func As IXMLDOMElement
Dim businessRules As IXMLDOMElement
Dim businessRule(100) As IXMLDOMElement
Dim displayOptions As IXMLDOMElement
Dim precision As IXMLDOMElement
Dim display As IXMLDOMElement
Dim printOptions As IXMLDOMElement
Dim pageSettings As IXMLDOMElement
Dim format As IXMLDOMElement
Dim printDisplay As IXMLDOMElement
Dim smartViewOptions As IXMLDOMElement

Dim dataValidationRules As IXMLDOMElement
Dim dataValidationRulesOptions As IXMLDOMElement
Dim RunValidationsAsLoggedInUser As IXMLDOMElement
Dim ValidateOnlyForPagesWithBlocks As IXMLDOMElement
Dim ValidateOnlyForUsersWithAccessToForm As IXMLDOMElement

Dim dataValidationCond(100) As IXMLDOMElement
Dim dataValidationRule(100) As IXMLDOMElement
Set xmlDoc = New DOMDocument

xmlDoc.LoadXML ("<?xml version=""1.0"" encoding=""UTF-8""?><form xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""></form>")
Set Root = xmlDoc.DocumentElement
    Rem ............................................................................................
    Rem ............................................................................................
    Rem ............................................................................................
    Rem ............................................................................................
    Rem ............................................................................................
    strOutputPath = localpath & TableFile & "\" & AppName & "_" & TableName & ".xml"
Call FormatXmlDocument(xmlDoc) ' 将生成的xml文件格式化
xmlDoc.Save strOutputPath
Set xmlDoc = Nothing

    End Sub