Excel Mac 2016，在VBA函数内部调用ListObject时无法工作

Question

我正在将外部数据库中的数据调用到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

Answer 1

当我将记录带入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