Excel VBA: 迟绑定引用

Question

Excel VBA: 迟绑定引用

3

我正在为Excel插件编写一些代码，该代码从SQL Server中提取一些数据。代码本身运行得很完美，但不知何故某些东西已经损坏了。

似乎代码会正常工作几次，然后突然触发Excel崩溃。经过很长时间的研究，我确定这与引用有关，因为如果在崩溃时我将引用“Microsoft ActiveX Data Objects 2.8 Library”更改为其他内容，然后再次更改回来，则插件将再次工作。

因为重建插件不起作用，所以我开始探索使用late-binding的选项。只是我似乎无法理解如何做到这一点。

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

长话短说：我只想让这个插件自动添加引用“Microsoft ActiveX Data Objects 2.8 Library”。

非常感谢您的帮助！

- TroelsH

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Degustaf · Accepted Answer

回答你有关晚期绑定的问题，这涉及到替换代码行。

Dim Connection As ADODB.Connection

使用

Dim Connection As object

以及替换

Set Connection = New ADODB.Connection

使用

Set Connection = GetObject(, "ADODB.Connection")

同样地，对于该库中的其他对象也是如此。

现在，我不确定这是否能解决你实际遇到的问题。听起来像是ActiveX库中存在一个bug，而你则恰好遇到了它，尽管你所做的事情并不特别离奇。