Excel vba 使用 ADO 获取结果集到 listobject
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10693867/
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 ADO to get the result set to listobject
提问by Ivan Chan
Hello I got the error [Runtime Error '448': Named argument not found] in the line
您好，我在行中收到错误 [Runtime Error '448': Named argument not found]
Set qt = ThisWorkbook.Sheets(server_hostname).ListObjects.Add(Connection:=oRS, _
Destination:=ThisWorkbook.Sheets(server_hostname).Range("A1")).QueryTable
The full source code as follows,
完整源代码如下，
Sub getavgcpu(server_hostname)
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable
ThisWorkbook.Sheets(server_hostname).Activate
ConnString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost; _
Database=test; User=root;Password=123456;Option=3;"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open
SQL = "SELECT cpu_max_unix_0.LOGDATE as 'Date of Month', cpu_max_unix_0.CPU as 'CPU Utilization %' FROM test.cpu_max_unix cpu_max_unix_0 where (cpu_max_unix_0.LOGDATE between '" & fromDateStr & "' and '" & toDateStr & "') And (cpu_max_unix_0.SERVER_NAME='" & server_hostname & "') Order By cpu_max_unix_0.LOGDATE"
Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open
'Set qt = ThisWorkbook.Sheets(server_hostname).QueryTables.Add(Connection:=oRS, _
'Destination:=ThisWorkbook.Sheets(server_hostname).Range("A1"))
Set qt = ThisWorkbook.Sheets(server_hostname).ListObjects.Add(Connection:=oRS, _
Destination:=ThisWorkbook.Sheets(server_hostname).Range("A1")).QueryTable
qt.Refresh
If oRS.State <> adStateClosed Then
oRS.Close
End If
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
End Sub
Please help!
请帮忙！
回答by Jim Sjoo
I tried your code with the first argument(SourceType) as following:
我使用第一个参数（SourceType）尝试了您的代码，如下所示：
Set qt = ThisWorkbook.Sheets(server_hostname).ListObjects.Add( _
SourceType:=XlListObjectSourceType.xlSrcQuery, _
Source:=oRS, _
Destination:=ThisWorkbook.Sheets(server_hostname).Range("A1")).QueryTable
and it works well.
它运作良好。
回答by Siddharth Rout
UNTESTED
未经测试
Try this
尝试这个
Dim ws As Worksheet
'
'~~> Rest of the code
'
Set ws = ThisWorkbook.Sheets(server_hostname)
Set objListObject = ws.ListObjects.Add(SourceType:=xlSrcExternal, Source:=oRS, _
LinkSource:=True, TableStyleName:=xlGuess, Destination:=ws.Range("A1"))
'
'~~> Rest of the code
'
Also please see this link by MS
另请参阅 MS 提供的此链接
TOPIC: ListObjects.Add Method (Excel)
主题：ListObjects.Add 方法 (Excel)
