Excel vba 使用变量作为 ListObjects 的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9866174/
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
Excel vba using variable as the name of ListObjects
提问by Ivan Chan
Function update_avgcpu_data(Server_hostname)
Dim rpt_name As String
rpt_name = Server_hostname & "avgcpu"
MsgBox rpt_name
With ThisWorkbook.Sheets(Server_hostname).ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=localtest;",Destination:=ThisWorkbook.Sheets(Server_hostname).Range("$A")).QueryTable
.CommandText = "SELECT cpu_avg_statistics_0.LOGDATE as 'Date of Month', cpu_avg_statistics_0.CPU as 'CPU Utilization %' FROM test.cpu_avg_statistics cpu_avg_statistics_0 WHERE (cpu_avg_statistics_0.SERVER_NAME='" & Server_hostname & "') AND (cpu_avg_statistics_0.LOGDATE between '2012-02-01' and '2012-02-05') ORDER BY cpu_avg_statistics_0.LOGDATE"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = rpt_name
.Refresh BackgroundQuery:=False
End With
End Function
I use the above function to run the query, the error code is run-time error 1004 , application-defined or object-defined error.When press debug , it stopped at the line .ListObject.DisplayName = rpt_name. Please help
我使用上面的函数运行查询，错误代码是运行时错误 1004 ，应用程序定义或对象定义错误。当按下 debug 时，它停在 .ListObject.DisplayName = rpt_name 行。请帮忙
回答by 6mtg35
I ran into the same problem when I used a macro to create essentially the same script you noted. After a hour or two of debugging, I came up with this:
当我使用宏创建与您注意到的脚本基本相同的脚本时，我遇到了同样的问题。经过一两个小时的调试，我想出了这个：
Short answeris that you have an object already named rpt_name. Since you cannot have two objects with the same name excel throws an error.
简短的回答是您已经有一个名为rpt_name的对象。由于您不能有两个同名的对象，因此 excel 会引发错误。
Long answerI got this from the VBA help function: "If an attempt is made to set the Name property to a name already used by another ListObject object, a run-time error is thrown."
我从 VBA 帮助函数中得到了很长的答案：“如果尝试将 Name 属性设置为另一个 ListObject 对象已使用的名称，则会引发运行时错误。”
Longer answerfor me my error was that I was creating a new table everytime I ran my macro and the macro tried to use the same name for the new table. Of course the first time I ran the macro it worked because it didn't have a duplicate name, but subsequent runs resulted in a crash because of the duplicate name (in my case a table name)
对我来说更长的答案是我的错误是每次运行宏时我都在创建一个新表，并且宏尝试对新表使用相同的名称。当然，我第一次运行宏时，它起作用了，因为它没有重复的名称，但随后的运行由于名称重复而导致崩溃（在我的例子中是表名）
Longest answerat this point I'm guessing that you may have entered the same rpt_namein your message box (third line of your script?) resulting in the error. You'll probably need to compare what was entered into the message box with a list of existing names before you continue with your script...
目前最长的答案我猜您可能在消息框中输入了相同的rpt_name（脚本的第三行？）导致错误。在继续执行脚本之前，您可能需要将输入到消息框中的内容与现有名称列表进行比较...
回答by Sohel
I was having the same error earlier today. To resolve it I did the following:
我今天早些时候遇到了同样的错误。为了解决它，我做了以下事情：
Before the importing of access table I have added a sheet
ActiveWorkbook.Worksheets.AddAccess Import code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _................ ......... .........Rename the sheet. The following code renames the Active sheet to
AccessImportActiveSheet.Name = "AccessImport"
在导入访问表之前，我添加了一张表
ActiveWorkbook.Worksheets.Add访问导入代码：
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _................ ......... .........重命名工作表。以下代码将活动表重命名为
AccessImportActiveSheet.Name = "AccessImport"
回答by MrExcel
Probably your variable "Server_hostname" contains spaces or other characters that are not allowed to use for your table display name.
可能您的变量“Server_hostname”包含不允许用于表显示名称的空格或其他字符。
Try the same code but change the value of rpt_name. For example rpt_name = "avgcpu"
尝试相同的代码，但更改 rpt_name 的值。例如 rpt_name = "avgcpu"
