如何将多个其他格式的文件合并到一个excel里去

如何将多个其他格式的文件合并到一个excel里去

1、在含有这多个文件的文件夹下新建一个空白的.excel文件，如图新建总表.xlsx

2、打开总表，按住alt+F8;在弹出的对话框里定义宏名字，并点击创建

 

 

 

 

 

 

代码如下：

Sub cc()

 

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 & "\" & "*.csv")    //敲重点

 

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("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)

 

For G = 1 To Sheets.Count

 

Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)

 

Next

 

WbN = WbN & Chr(13) & Wb.Name

 

Wb.Close False

 

End With

 

End If

 

MyName = Dir

 

Loop

 

Range("B1").Select

 

Application.ScreenUpdating = True

 

MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

 

End Sub