MS EXCEL VBA - 我需要将工作表从一个 Excel 文件导入到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9790451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS EXCEL VBA - I need to import a worksheet from one excel file to another
提问by Larry
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
回答by Matt Demaine
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
将变量的所有声明移到过程的开头，以便在使用它们之前声明它们。这只是很好的做法。
在打开第二个工作簿之前将活动工作簿分配给变量，因此只有一个工作簿打开。
您对每个语句也进行了一些更正。
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
回答by Abe Gold
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
