VBA将XML文件转换为一个CSV [英] VBA to convert XML files to one 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屋！