很多软件和网站会有导出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),点击对话框中的运行,这时候我们只需要静待程序批量处理完即可,中间不要进行任何操作,最后运行完毕后确定即可。
发表回复