经常要把下载下来的csv格式的文件处理成xlsx格式,才能进行各种函数处理,如果有成千上百个csv文件那就会很费时间,以下宏代码保哥在office2016上亲测可用,现分享给大家,要注意源文件夹与目标文件夹的绝对路径要正确,并且最后的“\”不要丢:
Sub CSVTOXLSX() Dim fDir As String Dim wB As Workbook Dim wS As Worksheet Dim fPath As String Dim sPath As String fPath = "C:\Users\Micro\Desktop\source\" sPath = "C:\Users\Micro\Desktop\target\" fDir = Dir(fPath) Do While (fDir <> "") If Right(fDir, 4) = ".csv" Or Right(fDir, 5) = ".csv" Then On Error Resume Next Set wB = Workbooks.Open(fPath & fDir) 'MsgBox (wB.Name) For Each wS In wB.Sheets wS.SaveAs sPath & wB.Name & ".xlsx" _ , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Next wS wB.Close False Set wB = Nothing End If fDir = Dir On Error GoTo 0 Loop End Sub
发表回复