EXCLE如何通过宏功能将多个EXCLE表格进行重组并在同一个EXCLE的sheet1内进行展示
时间: 2023-05-15 10:05:53 浏览: 57
您可以使用宏功能中的VBA代码来实现将多个EXCEL表格重组并在同一个EXCEL的sheet1内进行展示。具体实现方法可以参考以下代码:
Sub CombineExcelFiles()
Dim Path As String, Filename As String, Sheet As String, Total As Integer
Dim wb As Workbook, DestWb As Workbook
Dim ws As Worksheet, DestWs As Worksheet
Dim LastRow As Long, LastCol As Long, StartRow As Long
Dim i As Integer, j As Integer, k As Integer
'设置源文件路径
Path = "C:\Excel Files\"
'设置目标文件名
Filename = "Combined.xlsx"
'设置目标工作表名
Sheet = "Sheet1"
'创建目标工作簿
Set DestWb = Workbooks.Add
'循环遍历源文件夹中的所有文件
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
'打开源文件
Set wb = Workbooks.Open(Path & Filename)
'循环遍历源工作簿中的所有工作表
For Each ws In wb.Worksheets
'获取源工作表的最后一行和最后一列
LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
'获取目标工作表的起始行
If Total = 0 Then
StartRow = 1
Else
StartRow = DestWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
End If
'将源工作表的数据复制到目标工作表中
Set DestWs = DestWb.Worksheets(Sheet)
ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol)).Copy DestWs.Cells(StartRow, 1)
'更新总行数
Total = Total + LastRow
'关闭源工作簿
wb.Close False
Next ws
'获取下一个源文件名
Filename = Dir()
Loop
'保存目标工作簿
DestWb.SaveAs Filename:=Path & Filename, FileFormat:=xlOpenXMLWorkbook
'关闭目标工作簿
DestWb.Close False
'提示合并完成
MsgBox "合并完成!"
End Sub