我正在将来自外部数据库的数据调用到ListObject表中。该脚本在Sub（）中运行时创建ListObject表没有问题，但是在Function内部调用时，该脚本只是在ListObject行中停止，因此报告Application-defined or object-defined error。脚本如下：
Function get_value(input_id As String, input_date As String)
On Error GoTo xerr
Dim sqlstring As String
Dim connstring As String
Dim sLogin As String
sLogin = "DATABASE=DB;UID=UID;PWD=PWD"
sqlstring = "SELECT data_date, data_value FROM tb_data_values WHERE series_id='" & input_id & "' AND data_date<='" & input_date & _
"' ORDER BY data_date DESC"
connstring = "ODBC;DSN=myodbc;" & sLogin
Dim qtTable As QueryTable
Set qtTable = Worksheets("hs").ListObjects.Add(SourceType:=xlSrcExternal, Source:=connstring, Destination:=Worksheets("hs").Range("A1")).QueryTable
With qtTable
.ListObject.ShowHeaders = False
.ListObject.ShowTableStyleRowStripes = False
.ListObject.ShowTableStyleColumnStripes = False
.ListObject.ShowTableStyleFirstColumn = False
.CommandText = sqlstring
.CommandType = xlCmdSql
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.Refresh
End With
get_value=Worksheets("hs").ListObjects(1).Cells(1,2)
Exit Function
xerr:
Debug.Print err.Description
End Function
最佳答案
将记录带入Excel表时，我使用Connections
这是一个例子：
'Query the database
Sheets("FullRecordFromSS").Select
ActiveSheet.Unprotect
DoEvents
Range("Table_ExternalData_1[ID]").Select
Let stSQL1 = "SELECT * FROM libInfo.dbo.tblMain where LibUserID = " & "'" & SelectedLibUserID & "'" & ";"
Range("A2").Select
With ActiveWorkbook.Connections("GetRecordToUpdate").ODBCConnection
.BackgroundQuery = True
.CommandText = stSQL1
.CommandType = xlCmdSql
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=db1d.acsu.buffalo.edu,14360;UID=libinfo;PWD=inf014lib;APP=Microsoft Office 2016;WSID=LIBJBG6281"
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
关于mysql - Excel Mac 2016，在VBA函数内调用时ListObject不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53084611/