VBA将XML文件转换为一个CSV [英] VBA to convert XML files to one CSV
本文介绍了VBA将XML文件转换为一个CSV的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Sub xmltoxl()
Dim f As String
Dim wbk As Workbook
Dim s As Integer
f = Dir("C:\Users\Kanye\Downloads" & "\*.xml")
s = 0
Do While Len(f) > 0
s = s + 1
Set wbk = Workbooks.OpenXML("C:\Users\Kanye\Downloads" & "\" & f)
wbk.SaveAs Filename:="C:\Users\Kanye\Downloads\Test" & s & ".csv"
wbk.Close False
f = Dir()
Loop
End Sub
我有900个XML文件。此代码获取一个XML文件,并将其转换为一个CSV。有没有办法将其转换为一个总CSV?
I have 900 XML files. This code takes one XML file and converts it to one CSV. Is there a way to convert it to one total CSV?
我尝试下面的过程,但是打开它时数据已损坏(我收到此错误: http://imgur.com/SiyiGag )
I try the process below but the data is corrupted when opening it (I get this error: http://imgur.com/SiyiGag)
cd /Users/kanye/Desktop/XML/unzipped
copy *.csv newfile.csv
这最终给了我这样的数据 http://imgur.com/zK0R2UT
This ends up giving me the data as such http://imgur.com/zK0R2UT
推荐答案
查看是否可行。
Sub xmltoxl()
Dim f As String
Dim wbk As Workbook
Dim s As Integer
Dim tBook As Workbook
Dim MySht As Worksheet
Set tBook = ThisWorkbook
Set MySht = tBook.Sheets(1)
MySht.Cells.ClearContents
f = Dir("C:\Users\Kanye\Downloads" & "\*.xml")
s = 0
Do While Len(f) > 0
Set wbk = Workbooks.OpenXML("C:\Users\Kanye\Downloads" & "\" & f)
If s = 0 Then
wbk.Sheets(1).Cells.Copy Destination:=MySht.Cells
LastRow = MySht.Range("A" & Rows.Count).End(xlUp).Row
MySht.Range("Z1:Z" & LastRow) = f
Else
LastRow = MySht.Range("A" & Rows.Count).End(xlUp).Row
NextRow = LastRow + 1
wbkLastRow = wbk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
wbk.Sheets(1).Rows("1:" & wbkLastRow).Copy Destination:=MySht.Rows(NextRow)
NewLastRow = MySht.Range("A" & Rows.Count).End(xlUp).Row
MySht.Range("Z" & NextRow & ":Z" & NewLastRow) = f
End If
MySht.Columns("Z").Cut
MySht.Columns("A").Insert
s = s + 1
wbk.SaveAs Filename:="C:\Users\Kanye\Downloads\Test" & s & ".csv"
wbk.Close False
f = Dir()
Loop
End Sub
这篇关于VBA将XML文件转换为一个CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文