怎么把100多个EXCEL文件合并成一个

2024-11-04 03:57:56
推荐回答(5个)
回答(1):

可以通过更改excel代码来合并多个文件。

详细步骤:

1、新建一个文件夹。

2、将要合并的表格放到里面。

3、新建一个表格。

4、用excel打开。

5、右击Sheet1。

6、选择查看代码。

7、将下列代码复制到文本框中:

Sub 合并当前目录下所有工作簿的全部工作表()

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("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


8、点击运行,即可成功合并。



参考资料:

百度百科-Microsoft Office Excel

回答(2):

操作设备:戴尔电脑

操作系统:win10

操作软件:Excel2016

1将需合并的Excel文件放在同一个文件夹中。

2打开新建的Excel文件,按 Alt + F11 键,打开宏,选择视图→代码窗口。

3、将下面的代码拷贝粘贴到代码窗口中:

Sub sheets2one()

'定义对话框变量

Dim cc As FileDialog

Set cc = Application.FileDialog(msoFileDialogFilePicker)

Dim newwork As Workbook

Set newwork = Workbooks.Add

With cc

If .Show = -1 Then

Dim vrtSelectedItem As Variant

Dim i As Integer

i = 1

For Each vrtSelectedItem In .SelectedItems

Dim tempwb As Workbook

Set tempwb = Workbooks.Open(vrtSelectedItem)

tempwb.Worksheets(1).Copy Before:=newwork.Worksheets(i)

newwork.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")

tempwb.Close SaveChanges:=False

i = i + 1

Next vrtSelectedItem

End If

End With

Set cc = Nothing

End Sub

4、点击菜单“运行”—“运行子过程/用户窗体”。

5、在弹出选择对话框中,选择要批量合并的Excel文件,点击“确定”即可合并为一个Excel工作簿。

6、点击“确定”后,运行合并即可。

回答(3):

合并方法如下:

1.需要把多个excel表都放在同一个文件夹里面,并在这个文件夹里面新建一个excel。如图所示:

2.用microsoft excel打开新建的excel表,并右键单击sheet1,找到“查看代码”,单击进去。进去之后就看到了宏计算界面。如图所示:

3.然后把下面这些宏计算的代码复制进去,然后找到工具栏上面的“运行”下的“运行子过程/用户窗体”,代码如下,如图所示:


Sub 合并当前目录下所有工作簿的全部工作表()

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("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

4.运行之后,等待10秒针左右,等运行完毕,就是合并完成之后,会有提示,点确定就可以了。查看合并后的数据,有5000多行,就是同一个文件夹里面17个excel表数据合并后的结果。效果如图所示。

回答(4):

5分钟学会在Excel中如何把多个工作表合并成一个表 N常用的技能

回答(5):

新建一个工作表,命名后保存到和与合并的100个文件同一个文件文件夹,摁 alt + f11,双击工程资源管理器里面的sheet1(sheet1),在右侧的代码区粘贴如下代码。运行。等候一会就OK了。

Sub 合并当前目录下所有工作簿的全部工作表()
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 Sub