如何引用 Excel 工作表而不在 VBA 中对其名称进行硬编码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10512772/
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
How can I reference an Excel worksheet without hardcoding its name in VBA?
提问by user1384064
I am creating a Macro for excel for copying one excel sheet to another excel sheet.
我正在为 excel 创建一个宏，用于将一个 Excel 工作表复制到另一个 Excel 工作表。
This is working:
这是有效的：
Function CopyFile()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set Wb1 = ActiveWorkbook
Set ws1 = Wb1.Worksheets("Task")
Set Wb2 = Workbooks.Open("D:\Outlook\dest.xlsx")
ws1.Copy Wb2.Worksheets(1)
Wb2.Save
Wb2.Close
Wb1.Activate
End Function
But i don't want to use a hard coded string to get the sheet name Set ws1 = Wb1.Worksheets("Task"). Instead want to use the function argument.
但我不想使用硬编码字符串来获取工作表名称 Set ws1 = Wb1.Worksheets("Task")。而是想使用函数参数。
Following piece of code does not work:
以下代码不起作用：
Function CopyFile(name As String)
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set Wb1 = ActiveWorkbook
Set ws1 = Wb1.Worksheets(name)
Set Wb2 = Workbooks.Open("D:\Outlook\dest.xlsx")
ws1.Copy Wb2.Worksheets(1)
Wb2.Save
Wb2.Close
Wb1.Activate
End Function
回答by Dick Kusleika
It works for me. If you pass a name that doesn't exist, you'll get an error. Maybe
这个对我有用。如果传递的名称不存在，则会出现错误。也许
Function CopyFile(sSheetName As String)
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Set wbSource = ActiveWorkbook
On Error Resume Next
Set wsSource = wbSource.Worksheets(sSheetName)
On Error GoTo 0
If Not wsSource Is Nothing Then
Set wbDest = Workbooks.Open("D:\Outlook\dest.xlsx")
wsSource.Copy wbDest.Worksheets(1)
wbDest.Save
wbDest.Close
wbSource.Activate
End If
End Function
The work "Name" is a reserved word in VBA. You can still use it as a variable/argument name, but you probably shouldn't.
作品“名称”是 VBA 中的保留字。您仍然可以将其用作变量/参数名称，但您可能不应该这样做。
