Excel VBA：后期绑定引用

本文介绍了Excel VBA：后期绑定引用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在为excel中的一个加载项编写一些代码，该代码从SQL Server中获取一些数据。代码本身正常工作，但不知何故已损坏。

似乎代码可以正常工作几次，然后突然触发excel崩溃。很久以后，我确定它与引用有关，看到如果在崩溃时，我将参考Microsoft ActiveX数据对象2.8库更改为其他内容，然后再次返回，加载项将工作再次。

看到重建加载项不起作用，我开始探索后期绑定的选项。我似乎无法理解如何做到这一点。

  Private Sub RetrieveToWorksheet（SQL As String，WriteTo As Range，Optional WriteColumnNames As Boolean = True）
 
如果GetStatus =True然后
 MsgBox（数据库正在更新，请稍后重试）
退出子
结束如果
 
应用程序。 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 
 
错误GoTo Finalize 
 Err.Clear 
设置连接=新建ADODB.Connection 
 Connection.ConnectionTimeout = 300 
 Connection.CommandTimeout = 300 
 Connection.ConnectionString =Provider = sqloledb; Data Source = vdd1xl0001;初始目录= SRDK;用户ID = SRDK_user;密码=密码;连接超时= 300
 Connection.Mode = adModeShareDenyNone 
 Connection.Open 
 Set RecordSet = New ADODB.RecordSet 
 RecordSet.CursorLocation = adUseServer 
 RecordSet.Open SQL，C连接，ADODB.CursorTypeEnum.adOpenForwardOnly 
 RowOffset = 0 
 ColumnOffset = 0 
 
如果WriteColumnNames = True然后
对于RecordSet.Fields中的每个字段
 WriteTo .Cells（1，1）.Offset（RowOffset，ColumnOffset）.Value = Field.Name 
 ColumnOffset = ColumnOffset + 1 
下一个
 ColumnOffset = 0 
 RowOffset = 1 
 End If 
 
 WriteTo.Cells（1，1）.Offset（RowOffset，ColumnOffset）.CopyFromRecordset RecordSet 
 
 Finalize：
 
如果不是RecordSet Is Nothing Then 
如果没有RecordSet.State = ADODB.ObjectStateEnum.adStateClosed Then RecordSet.Close 
 Set RecordSet = Nothing 
 End If 
如果没有连接是没有
如果不是Connection.State = ADODB.ObjectStateEnum.adStateClosed Then Connection.Close 
 Set Connection = Nothing 
 End If 
如果Err.Number<> 0 Then Err.Raise Err.Number，Err.Source，Err.Description 
 End Sub

长篇小说：我只想让加载项自动添加引用Microsoft ActiveX数据对象2.8库。

所有帮助都非常感谢！

解决方案

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

  Dim Connection As ADODB.Connection

with

  Dim Connection As object

并替换

Set Connection = New ADODB.Connection / pre>

与

  Set Connection = GetObject（，ADODB 。连接）

同样来自该库的其他对象。

现在，我不知道这是否会解决您遇到的实际问题。这听起来像是在ActiveX库中有一个错误，你正在击中它，尽管你所做的一切似乎非常深奥。

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.

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.

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'.

All help is greatly appreciated!

解决方案

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.

这篇关于Excel VBA：后期绑定引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！