VBA合并所有xlsx文件

2021-07-06 21:16:43  阅读 204 次 评论 0 条

以下代码测试成功



Option Explicit

Sub mergeonexls() '合并多工作簿中指定工作表

On Error Resume Next

Dim x As Variant, x1 As Variant, w As Workbook, wsh As Worksheet

Dim t As Workbook, ts As Worksheet, l As Integer, h As Long

Application.ScreenUpdating = False

Application.DisplayAlerts = False

x = Application.GetOpenFilename(FileFilter:="Excel文件 (*.xls; *.xlsx),*.xls; *.xlsx,所有文件(*.*),*.*", _

       Title:="Excel选择", MultiSelect:=True)

Set t = ThisWorkbook

Set ts = t.Sheets(1) '指定合并到的工作表,这里是第一张工作表

l = ts.UsedRange.SpecialCells(xlCellTypeLastCell).Column

For Each x1 In x

If x1 <> False Then

 Set w = Workbooks.Open(x1)

 Set wsh = w.Sheets(1) '指定所需合并工作表,这里是第一张工作表

 h = ts.UsedRange.SpecialCells(xlCellTypeLastCell).Row

 If l = 1 And h = 1 And ts.Cells(1, 1) = "" Then

 wsh.UsedRange.Copy ts.Cells(1, 1)

 Else

 wsh.UsedRange.Copy ts.Cells(h + 1, 1)

 End If

 w.Close

End If

Next

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

WPS达师专注于免费分享WPS Office使用教程、PPT、word模板及办公常用软件等资源,欢迎大家收藏和分享本站。
WPS表格数据付费处理请加QQ:3247742

发表评论


表情

还没有留言,还不快点抢沙发?