使用 VBA 将另一个工作簿中的工作表加载到 Excel 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11978711/
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
Loading a worksheet from another workbook into Excel with VBA
提问by AME
I am attempting to create a subroutine that prompts the user to select a workbook and then adds the first worksheet of the selected workbook as a tab in the existing (active) workbook. Then names the new tab "Data". Here is the code I am using so far:
我正在尝试创建一个子例程，提示用户选择一个工作簿，然后将所选工作簿的第一个工作表添加为现有（活动）工作簿中的选项卡。然后将新选项卡命名为“数据”。这是我目前使用的代码：
Sub getworkbook()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
ws = Application.GetOpenFilename(filter, , caption)
ws.Add After:=Sheets(Sheets.Count)
ws.Name = "DATA"
End Sub
This code doesn't seem to be working and is returning the following error:
此代码似乎不起作用，并返回以下错误：
"ws.Add" method or With Block not set.
“ws.Add”方法或未设置块。
Any help is appreciated.
任何帮助表示赞赏。
Thanks,
谢谢，
回答by Siddharth Rout
You have declared wsas a worksheet and GetOpenFilenameis returning a File name. I would recommend reading my post in this link:
您已声明ws为工作表并GetOpenFilename返回文件名。我建议阅读我在此链接中的帖子：
Is this what you are trying?
这是你正在尝试的吗？
Note: I have not done any error handling. I am sure you can take care of that.
注意：我没有做任何错误处理。我相信你可以解决这个问题。
Sub getworkbook()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
Caption = "Please Select an input file "
Ret = Application.GetOpenFilename(filter, , Caption)
If Ret = False Then Exit Sub
Set wb = Workbooks.Open(Ret)
wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
ActiveSheet.Name = "DATA"
End Sub
