VBA将多个工作表导入到工作簿 [英] VBA Import multiple sheets into Workbook

问题描述

我有以下代码，可以选择一个文件并从中导入笔"标签，但是我想选择多个文件.

I have the following code which allows me to select a single file and import the "Pens" tab from it, however I would like to select multiple files.

我希望能够从多个工作簿中选择笔"选项卡，每个工作簿都位于合并工作簿中自己的选项卡上.

I would like to be able to select the "Pens" tab from multiple workbooks each on its own tab in the consolidated workbook.

您能协助它如何工作吗?我认为这可能需要使用For Each函数，但不确定如何构造它.

Could you please assist in how this might work? I think this might require the use of the For Each function but not sure how to structure this.

非常感谢

Sub ImportActiveList()
    Dim FileName As String
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim ActiveListWB As Workbook

    Set WS2 = ActiveWorkbook.Sheets("AllPens")
    FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                           Title:="Select Active List to Import", _
                                           MultiSelect:=False)

    If FileName = "False" Then
        Exit Sub
    Else
        Set ActiveListWB = Workbooks.Open(FileName)
    End If

    Set WS1 = ActiveListWB.Sheets("Pens")

    WS1.UsedRange.Copy WS2.Range("A1")

    ActiveWorkbook.Close False

End Sub

推荐答案

如果您有要迭代的集合或对象或值的数组，则需要一个For Each循环.有关语法和用法示例，请参见文档.

You need a For Each loop if you have a collection or an array of objects or values that you want to iterate. See the documentation for the syntax and examples of its usage.

如果更改GetOpenFilename()的MultiSelect参数，则用户可以从同一目录中选择多个文件.返回值是包含所有这些文件的集合.然后，您可以像这样迭代它:

If you change the MultiSelect parameter for GetOpenFilename(), the user can select multiple files from the same directory. The return value is a collection containing all those files. You can then iterate it like this:

Public Sub ImportActiveList()
    Dim FileNames As Variant
    Dim FileName As Variant
    Dim WSNew As Worksheet
    Dim ActiveListWB As Workbook

    ' ask the user for the files to copy the data from
    FileNames = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                               Title:="Select Active List to Import", _
                                               MultiSelect:=True)
    If VarType(FileNames) = vbBoolean Then
        If Not FileNames Then Exit Sub
    End If

    ' loop over all files selected by the user and import the desired sheets
    For Each FileName In FileNames
        ' create new worksheet to copy the data to
        ' here you could add a name for the sheet or make sure it is at the right position
        Set WSNew = ActiveWorkbook.Sheets.Add

        ' copy the data
        Set ActiveListWB = Workbooks.Open(FileName)
        ActiveListWB.Sheets("Pens").UsedRange.Copy WSNew.Range("A1")
        ActiveListWB.Close False
    Next FileName
End Sub

还应注意以下几点:

• FileName = "False"仅在英语Excel安装中有效，因为其他语言对于False具有其他文字.另外，您将无法打开仅名为"False"的文件，因为您无法区分文件名和中止的文件对话框的返回值(尽管在大多数情况下，这实际上不是问题). .
  将类型为Variant的返回值保存在类型为String的变量中.如果将其也更改为Variant，则可以测试内容是否为子类型Boolean，以及该布尔值是否为False.这样可以避免上面提到的所有问题.
• ActiveWorkbook.Close关闭当前工作簿-这通常是刚刚打开以从中复制数据的工作簿.但是，假设您暂停了代码，切换到统一工作簿并继续执行代码:那么活动工作簿现在就是该工作簿，它将被关闭-无需提示保存！ 您真正想要的是关闭刚刚打开的工作簿，所以我用ActiveListWB替换了ActiveWorkbook.

• FileName = "False" will only work in an english Excel installation since other languages have other literals for False. Also you won't be able to open a file just named "False" because you cannot distinguish between the filename and the return value for an aborted file dialog (this won't be really a problem in most cases, though...).
  You save the return value which is of type Variant in a variable that is of type String. If you change that to be Variant as well you can test if the content is of sub type Boolean and if that boolean evaluates to False. This will avoid all problems mentioned above.
• ActiveWorkbook.Close closes the current workbook - which is most times the workbook that was just opened to copy the data from. But assume you halt the code, switch to the consolidated workbook and continue the code: Then the active workbook is now this workbook and it will be closed - without prompting to save!
  What you really want is to close the workbook you just opened, so I replaced ActiveWorkbook by ActiveListWB.

这篇关于VBA将多个工作表导入到工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！