Excel VBA：后期绑定参考
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26604111/
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: Late binding reference
提问by TroelsH
I'm trying to write some code for an add-in in excel, which grabs some data from an SQL Server. The code itself is working flawlessly, but somehow something got corrupted.
我正在尝试为 excel 中的加载项编写一些代码，它从 SQL Server 中获取一些数据。代码本身运行完美，但不知何故损坏了。
It seems that the code will work fine a few times and then all of a sudden trigger an excel-crash. After a long time I've determined that it has something to do with the references, seeing as if upon crash I change the reference 'Microsoft ActiveX Data Objects 2.8 Library' to something else, and then back again, the add-in will work again.
代码似乎可以正常工作几次，然后突然触发 excel-crash。很长一段时间后，我确定它与引用有关，好像在崩溃时我将引用“Microsoft ActiveX Data Objects 2.8 Library”更改为其他内容，然后再次返回，加载项将起作用再次。
Seeing as rebuilding the add-in doesn't work, I'm beginning to explore the option of late-binding. I just can't seem to understand how to do it.
看到重建加载项不起作用，我开始探索后期绑定的选项。我似乎无法理解该怎么做。
Private Sub RetrieveToWorksheet(SQL As String, WriteTo As Range, Optional WriteColumnNames As Boolean = True)
If GetStatus = "True" Then
MsgBox ("Database is currently being updated. Please try again later.")
Exit Sub
End If
Application.ScreenUpdating = False
Dim Connection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Dim Field As ADODB.Field
Dim RowOffset As Long
Dim ColumnOffset As Long
On Error GoTo Finalize
Err.Clear
Set Connection = New ADODB.Connection
Connection.ConnectionTimeout = 300
Connection.CommandTimeout = 300
Connection.ConnectionString = "Provider=sqloledb;Data Source=vdd1xl0001;Initial Catalog=SRDK;User Id=SRDK_user;Password=password;Connect Timeout=300"
Connection.Mode = adModeShareDenyNone
Connection.Open
Set RecordSet = New ADODB.RecordSet
RecordSet.CursorLocation = adUseServer
RecordSet.Open SQL, Connection, ADODB.CursorTypeEnum.adOpenForwardOnly
RowOffset = 0
ColumnOffset = 0
If WriteColumnNames = True Then
For Each Field In RecordSet.Fields
WriteTo.Cells(1, 1).Offset(RowOffset, ColumnOffset).Value = Field.Name
ColumnOffset = ColumnOffset + 1
Next
ColumnOffset = 0
RowOffset = 1
End If
WriteTo.Cells(1, 1).Offset(RowOffset, ColumnOffset).CopyFromRecordset RecordSet
Finalize:
If Not RecordSet Is Nothing Then
If Not RecordSet.State = ADODB.ObjectStateEnum.adStateClosed Then RecordSet.Close
Set RecordSet = Nothing
End If
If Not Connection Is Nothing Then
If Not Connection.State = ADODB.ObjectStateEnum.adStateClosed Then Connection.Close
Set Connection = Nothing
End If
If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.Description
End Sub
Long story short: I just want the add-in to automatically add the reference 'Microsoft ActiveX Data Objects 2.8 Library'.
长话短说：我只希望加载项自动添加引用“Microsoft ActiveX Data Objects 2.8 Library”。
All help is greatly appreciated!
非常感谢所有帮助！
回答by Degustaf
In answer to your question about late binding, this involves replacing the line of code
在回答您关于后期绑定的问题时，这涉及替换代码行
Dim Connection As ADODB.Connection
with
和
Dim Connection As object
and replacing
并更换
Set Connection = New ADODB.Connection
with
和
Set Connection = GetObject(, "ADODB.Connection")
And similarly for the other objects from that library.
对于该库中的其他对象也类似。
Now, I am not sure if this will fix the actual issue that you are having. It sounds like there is a bug in the ActiveX library and you are hitting it, although nothing you are doing seems particularly esoteric.
现在，我不确定这是否会解决您遇到的实际问题。听起来 ActiveX 库中有一个错误，您正在解决它，尽管您所做的一切似乎都不是特别深奥。
