Vba代码在同一工作簿的不同工作表中打开多个文件

Question

我有一个代码，允许我在 Excel 工作簿中打开文件，但是我希望能够在同一工作簿中打开名为 p00001、p00002、p00003 等的多个文件。有谁知道如何编辑代码以选择以这种方式命名的所有文件并在同一工作簿的单独工作表中打开它们？

我的代码是:

Sub Open_Workbook()

    Dim my_FileName As Variant

    my_FileName = Application.GetOpenFilename

    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If

End Sub

Answer 1

在此解决方案中，我使用 FileDialog 来选择多个文件。之后，您需要对所有这些文件进行 for 循环。在 For 循环内，您必须打开文件并导入工作表。在此示例中，我导入了工作簿具有的所有工作表。代码完成导入后，关闭源工作簿并对其余文件执行相同的操作。

Sub Import Files()
        Dim sheet As Worksheet
        Dim total As Integer
        Dim intChoice As Integer
        Dim strPath As String
        Dim i As Integer
        Dim wbNew As Workbook
        Dim wbSource As Workbook
        Set wbNew = Workbooks.Add


        'allow the user to select multiple files
        Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
        'make the file dialog visible to the user
        intChoice = Application.FileDialog(msoFileDialogOpen).Show

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        'determine what choice the user made
        If intChoice <> 0 Then
            'get the file path selected by the user
            For i = 1 To Application.FileDialog(msoFileDialogOpen).SelectedItems.Count
                strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(i)

                Set wbSource = Workbooks.Open(strPath)

                For Each sheet In wbSource.Worksheets
                    total = wbNew.Worksheets.Count
                    wbSource.Worksheets(sheet.Name).Copy _
                    after:=wbNew.Worksheets(total)
                Next sheet

                wbSource.Close
            Next i
        End If

    End Sub

如果您想从目录中获取所有文件，您可以使用循环更改应用程序文件对话框，就像这样循环目录:

 directory = "c:\test\"
    fileName = Dir(directory & "*.xl??")
    Do While fileName <> ""
    'Put Code From For Loop here.
    Loop