首页 » 应用软件 » Office系列 » 正文

使用宏代码批量将csv文件合并在一个xslx表格文件里

很多软件和网站会有导出EXCEL表格的功能,但导出来的文件格式以csv格式较常见,有时需要将多个CSV文件合并在一个EXCEL表里,解决方法如下:

先将所有在合并的CSV文件放在同一件文件夹里,然后再在这个文件夹里新建一个xslx格式的EXCEL文件并打开,进入“开发工具”——“宏”,开启宏对话框后,宏名随便填写几个英文字母,点击右侧的“创建”,进入代码编写,复制以下代码粘贴进编辑框中:

Sub 格式转换()
Dim FilePath, MyFile, iPath As String
iPath = ThisWorkbook.Path
MyFile = Dir(iPath & "\*.CSV")
If MyFile <> "" Then
Do
On Error Resume Next
If MyFile = ThisWorkbook.Name Then MyFile = Dir
Workbooks.Open (iPath & "\" & MyFile)
MyFile = Replace(MyFile, ".csv", ".xlsx")
Name = "\" & MyFile
FilePath = iPath & Name
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:= _
xlNormal, CreateBackup:=False
Workbooks(MyFile).Close True
Application.ScreenUpdating = True
MyFile = Dir
Loop While MyFile <> ""
Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long
Dim Num As Long
Dim BOX As String
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xls")
AWbName = ActiveWorkbook.Name
Num = 0
Do While MyName <> ""
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
For G = 1 To Sheets.Count
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)
Next
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示"
End If
End Sub

粘贴完后关闭对话框,运行宏或者按下宏快捷键(Alt+F8),点击对话框中的运行,这时候我们只需要静待程序批量处理完即可,中间不要进行任何操作,最后运行完毕后确定即可。

发表评论