VBA Excel行到XML输出在不同的输出文件夹 [英] vba excel rows to xml output on different output folder
本文介绍了VBA Excel行到XML输出在不同的输出文件夹的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,我制作了一个VBA宏,该宏每行创建一个xml文件.问题是在默认文件夹位置"C:\ Users \ Dell \ Documents"中创建的xml文件如何更改默认输出目录?
Hello i have made a VBA macro that creates one xml file per row. The problem is that the xml files where created in the default folder location "C:\Users\Dell\Documents" how can i change the default out put directory?
Sub xmlPerRow()
sTemplateXML = _
"<?xml version='1.0'?>" + vbNewLine + "<data>" + vbNewLine + "<Grai>" + vbNewLine + "</Grai>" + vbNewLine + _
" <DayDateOut>" + vbNewLine + " </DayDateOut>" + vbNewLine + " <Filler>" + vbNewLine + " </Filler>" + vbNewLine + _
" <FillerCountry>" + vbNewLine + " </FillerCountry>" + vbNewLine + " <Retailer>" + vbNewLine + " </Retailer>" + vbNewLine + _
" <RetailerCountry>" + vbNewLine + " </RetailerCountry>" + vbNewLine + " <Days>" + vbNewLine + " </Days>" + vbNewLine + _
" <DayBack>" + vbNewLine + " </DayBack>" + vbNewLine + " <DateIn>" + vbNewLine + " </DateIn>" + vbNewLine + _
" <BrokenCode>" + vbNewLine + " </BrokenCode>" + vbNewLine + " <Broken>" + vbNewLine + " </Broken>" + vbNewLine + _
" <TotalCycles>" + vbNewLine + " </TotalCycles>" + vbNewLine + "</data>" + vbNewLine
Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False
With ActiveWorkbook.Worksheets(1)
lLastRow = .UsedRange.Rows.Count
For lRow = 2 To lLastRow
sFile = "Grai_" & .Cells(lRow, 1).Value & ".xml"
Dim sGrai As String
sGrai = CStr(.Cells(lRow, 1).Value)
sDayDateOut = .Cells(lRow, 2).Value
sFiller = .Cells(lRow, 3).Value
sFillerCountry = .Cells(lRow, 4).Value
sRetailer = .Cells(lRow, 5).Value
sRetailerCountry = .Cells(lRow, 6).Value
sDays = .Cells(lRow, 7).Value
sDayBack = .Cells(lRow, 8).Value
sDateIn = .Cells(lRow, 9).Value
sBrokenCode = .Cells(lRow, 10).Value
sBroken = .Cells(lRow, 11).Value
sTotalCycles = .Cells(lRow, 12).Value
doc.LoadXML sTemplateXML
doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)
doc.getElementsByTagName("DayDateOut")(0).appendChild doc.createTextNode(sDayDateOut)
doc.getElementsByTagName("Filler")(0).appendChild doc.createTextNode(sFiller)
doc.getElementsByTagName("RetailerCountry")(0).appendChild doc.createTextNode(sRetailerCountry)
doc.getElementsByTagName("Retailer")(0).appendChild doc.createTextNode(sRetailer)
doc.getElementsByTagName("Days")(0).appendChild doc.createTextNode(sDays)
doc.getElementsByTagName("DayBack")(0).appendChild doc.createTextNode(sDayBack)
doc.getElementsByTagName("DateIn")(0).appendChild doc.createTextNode(sDateIn)
doc.getElementsByTagName("BrokenCode")(0).appendChild doc.createTextNode(sBrokenCode)
doc.getElementsByTagName("Broken")(0).appendChild doc.createTextNode(sBroken)
doc.getElementsByTagName("TotalCycles")(0).appendChild doc.createTextNode(sTotalCycles)
doc.Save sFile
Next
End With
End Sub
推荐答案
根据Scott的评论,此处为调整后的答案:
Based on the comment of Scott, here the adjusted answer:
Sub xmlPerRow()
sTemplateXML = _
"<?xml version='1.0'?>" + vbNewLine + "<data>" + vbNewLine + "<Grai>" + vbNewLine + "</Grai>" + vbNewLine + _
" <DayDateOut>" + vbNewLine + " </DayDateOut>" + vbNewLine + " <Filler>" + vbNewLine + " </Filler>" + vbNewLine + _
" <FillerCountry>" + vbNewLine + " </FillerCountry>" + vbNewLine + " <Retailer>" + vbNewLine + " </Retailer>" + vbNewLine + _
" <RetailerCountry>" + vbNewLine + " </RetailerCountry>" + vbNewLine + " <Days>" + vbNewLine + " </Days>" + vbNewLine + _
" <DayBack>" + vbNewLine + " </DayBack>" + vbNewLine + " <DateIn>" + vbNewLine + " </DateIn>" + vbNewLine + _
" <BrokenCode>" + vbNewLine + " </BrokenCode>" + vbNewLine + " <Broken>" + vbNewLine + " </Broken>" + vbNewLine + _
" <TotalCycles>" + vbNewLine + " </TotalCycles>" + vbNewLine + "</data>" + vbNewLine
Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False
With ActiveWorkbook.Worksheets(1)
lLastRow = .UsedRange.Rows.Count
For lRow = 2 To lLastRow
sFile = "c:\GraiData\Grai_" & .Cells(lRow, 1).Value & ".xml"
Dim sGrai As String
sGrai = CStr(.Cells(lRow, 1).Value)
sDayDateOut = .Cells(lRow, 2).Value
sFiller = .Cells(lRow, 3).Value
sFillerCountry = .Cells(lRow, 4).Value
sRetailer = .Cells(lRow, 5).Value
sRetailerCountry = .Cells(lRow, 6).Value
sDays = .Cells(lRow, 7).Value
sDayBack = .Cells(lRow, 8).Value
sDateIn = .Cells(lRow, 9).Value
sBrokenCode = .Cells(lRow, 10).Value
sBroken = .Cells(lRow, 11).Value
sTotalCycles = .Cells(lRow, 12).Value
doc.LoadXML sTemplateXML
doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)
doc.getElementsByTagName("DayDateOut")(0).appendChild doc.createTextNode(sDayDateOut)
doc.getElementsByTagName("Filler")(0).appendChild doc.createTextNode(sFiller)
doc.getElementsByTagName("RetailerCountry")(0).appendChild doc.createTextNode(sRetailerCountry)
doc.getElementsByTagName("Retailer")(0).appendChild doc.createTextNode(sRetailer)
doc.getElementsByTagName("Days")(0).appendChild doc.createTextNode(sDays)
doc.getElementsByTagName("DayBack")(0).appendChild doc.createTextNode(sDayBack)
doc.getElementsByTagName("DateIn")(0).appendChild doc.createTextNode(sDateIn)
doc.getElementsByTagName("BrokenCode")(0).appendChild doc.createTextNode(sBrokenCode)
doc.getElementsByTagName("Broken")(0).appendChild doc.createTextNode(sBroken)
doc.getElementsByTagName("TotalCycles")(0).appendChild doc.createTextNode(sTotalCycles)
doc.Save sFile
Next
End With
End Sub
这篇关于VBA Excel行到XML输出在不同的输出文件夹的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文