怎样用vba合并同一目录下所有excel文件

答案:3 悬赏:0 手机版

解决时间 2021-11-19 23:47

提问者网友：謫仙
2021-11-19 15:24

最佳答案

五星知识达人网友：轻雾山林
2021-11-19 16:41

试试下面的代码：
Sub Macro1()
    Dim MyPath$, MyName$, sh As Worksheet, m&, w As WorksheetFunction, wb As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set w = WorksheetFunction
    MyPath = ThisWorkbook.Path & "采集多数据"
    MyName = Dir(MyPath & "*.xls*")
    [a1].CurrentRegion.Offset(1).ClearContents
    Do While MyName <> ""
            With GetObject(MyPath & MyName)
                With .Sheets(1)
                    If w.CountA(.UsedRange.Offset(1)) Then
                        m = m + 1
                        If m = 1 Then
                            Set wb = Workbooks.Add(xlWBATWorksheet)
                            Set sh = wb.ActiveSheet
                            .[a1].CurrentRegion.Copy sh.[a1]
                        Else
                            .[a1].CurrentRegion.Offset(1).Copy sh.[a65536].End(xlUp).Offset(1)
                        End If
                    End If
                End With
                .Close False
            End With
        MyName = Dir
    Loop
    wb.SaveAs Filename:=ThisWorkbook.Path & "采集多数据20130422.xls", FileFormat:=xlExcel8
    wb.Close
    Application.ScreenUpdating = True
    MsgBox "ok"
End Sub

全部回答

1楼网友：何以畏孤独
2021-11-19 18:01

具体怎样合并，请举例说明

2楼网友：玩世
2021-11-19 16:47

提示已合并，但表格中什么都没有

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息