MS EXCEL VBA - 我需要将一个excel文件中的工作表导入到另一个excel文件中

I need to import a worksheet from one excel workbook (worksheet name is not always the same) and import it into the current active workbook.

我需要从一个Excel工作簿导入工作表(工作表名称并不总是相同)并将其导入当前活动工作簿。

Here is what I have so far:

这是我到目前为止:

Sub openFile_Click()
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.rpt (*.rpt),")

    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Workbooks.Open Filename:=FileToOpen

        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Set wb1 = ActiveWorkbook
        wb2 = Workbooks(FileToOpen)     'This is where I am stuck..I can't give it a static name

        For Each Sheet In wb1.Sheets
            If Sheets.Visible = True Then
                Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
            End If

        Next Sheet

    End If

2 个解决方案

#1

This code will work for what you want you want. I made the following corrections.

此代码适用于您想要的内容。我做了以下更正。

Move all declarations of variables to beginning of procedure so they are declared before you use them. It is just good practice.

将所有变量声明移动到过程的开头,以便在使用它们之前声明它们。这只是一种很好的做法。
Assign your Active Workbook to the variable before you open the second workbook so there is only one workbook open.

在打开第二个工作簿之前将活动工作簿分配给变量,这样只打开一个工作簿。

Your for each statement had a few corrections as well.

您的每个声明也有一些更正。

Sub openFile_Click()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    For Each Sheet In wb2.Sheets
        If Sheet.Visible = True Then
            Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
        End If
    Next Sheet

End If

End Sub

#2

Set the Workbook on open, (or set the workbook later without the filepath)

将工作簿设置为打开,(或稍后在没有文件路径的情况下设置工作簿)

Here you go:

干得好:

Sub openFile_Click()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open(FileToOpen)




    For Each Sheet In wb1.Sheets
        If Sheet.Visible = True Then
            Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
        End If

    Next Sheet

End If
End Sub

#1