vba - 在不打开工作簿的情况下从多个工作簿复制特定工作表

Question

我在下面有一个代码，它从所有事件或打开的工作簿中复制特定的工作表。

但是如何在不打开工作簿的情况下复制同一个工作表，就像我们可以在代码中提供路径一样，它应该能够从该路径的所有工作簿中选择给定的工作表。

以下是当前正在使用的代码。

Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        If wkb.Name <> ThisWorkbook.Name Then
            wkb.Worksheets(sWksName).Copy _
            Before:=ThisWorkbook.Sheets(1)
        End If
    Next

    Set wkb = Nothing
 End Sub

Answer 1

与 合作Workbooks.Open Method 在后台打开它，并使用 Application/ScreenUpdating/EnableEvents/DisplayAlerts 隐藏任何警报

_{Application.ScreenUpdating Property (Excel) Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.}

例子

Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    wkb Workbooks.Open("C:\temp\bookname.xls")

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        wkb.Worksheets(sWksName).Copy _
        Before:=ThisWorkbook.Sheets(1)
    Next

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set wkb = Nothing
 End Sub

假设您的文件夹名称是 C:\Temp\然后循环直到文件夹返回一个空

例子

    Dim FileName As String
    ' Modify this folder path as needed
    FolderPath = "C:\Temp\"
    ' Call Dir the first time to all Excel files in path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty .
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set wkb = Workbooks.Open(FolderPath & FileName)

        '--->> Do your copy here

        ' Close the source workbook without saving changes.
        wkb.Close savechanges:=False

        ' next file name.
        FileName = Dir()
    Loop

_{Use a Do...Loop structure when you want to repeat a set of statements an indefinite number of times, until a condition is satisfied. If you want to repeat the statements a set number of times, the For...Next Statement is usually a better choice.}