vba 以编程方式添加引用

Add references programmatically

提问by Falcon

we have an Access-Application which does not work on some clients, mainly because references are broken. That happens for example when you start the access application with access runtime 2007 but have office in version 2003 or 2000 installed. Functions like Left/Right/Trim etc. just stop working then.

我们有一个在某些客户端上不起作用的访问应用程序，主要是因为引用被破坏了。例如，当您使用 access runtime 2007 启动 access 应用程序但安装了 2003 或 2000 版本的 office 时，就会发生这种情况。左/右/修剪等功能就停止工作了。

I think the only way to fix this problem is to programmtically check which office version is installed and add the references programmatically as in these heterogenous environments we cannot control what the user has installed. Specifically I need to reference the Microsoft Office Object libraries for Excel and Word.

我认为解决这个问题的唯一方法是以编程方式检查安装了哪个办公版本并以编程方式添加引用，因为在这些异构环境中我们无法控制用户安装了什么。具体来说，我需要引用 Excel 和 Word 的 Microsoft Office 对象库。

But I neither have the guids of all office versions nor have a clue how to check them automatically.

但我既没有所有办公版本的指南，也不知道如何自动检查它们。

采纳答案by Tony Toews

If you ship an MDE/ACCDE you can't update your references.

如果您提供 MDE/ACCDE，则无法更新您的参考。

But what specific references are causing you your problems? Chances are you are referencing Word, Excel or Outlook. If so use late binding so your solution doesn't matter what version is installed on the client system.

但是，哪些具体的参考资料会导致您遇到问题？您可能正在引用 Word、Excel 或 Outlook。如果是这样，请使用后期绑定，因此您的解决方案与客户端系统上安装的版本无关。

Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question. Rather than erroring out while starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

后期绑定意味着您可以安全地删除引用，并且只有在应用程序执行相关代码行时才会出现错误。而不是在启动应用程序时出错并且根本不允许用户进入应用程序。或者在击中中、左或修剪函数调用时。

This also is very useful when you don't know what version of the external application will reside on the target system. Or if your organization is in the middle of moving from one version to another.

当您不知道外部应用程序的哪个版本将驻留在目标系统上时，这也非常有用。或者，如果您的组织正在从一个版本迁移到另一个版本。

For more information including additional text and some detailed links see the "Late Binding in Microsoft Access" page.

有关更多信息，包括附加文本和一些详细链接，请参阅“ Microsoft Access 中的后期绑定”页面。

回答by TheLaurens

So yeah, this answer is a bit late, but just in case someone stumbles across this like I did looking for an answer, I figured out the following bit of code to add an excel reference and it seems to work fine, also in MDE/ACCDE!

所以是的，这个答案有点晚了，但以防万一有人像我寻找答案一样偶然发现了这个，我想出了以下代码来添加 excel 参考，它似乎工作正常，也在 MDE/ ACCDE！

If Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe")
End If
If Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") <> "" And Not refExists("excel") Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe")
End If
If Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") = "" And Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") = "" Then
    MsgBox ("ERROR: Excel not found")
End If

And the refExists references the following function:

并且 refExists 引用了以下函数：

Private Function refExists(naam As String)
Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

回答by Mark Mooibroek

Here is an example - it check for certain references - deleting them and importing the Access 2000 variant. Just to make sure all clients use the same (lowest) version of the dependencies

这是一个示例 - 它检查某些引用 - 删除它们并导入 Access 2000 变体。只是为了确保所有客户端使用相同（最低）版本的依赖项

Sub CheckReference()
' This refers to your VBA project.
    Dim chkRef As Reference ' A reference.

    Dim foundWord, foundExcel As Boolean

    foundWord = False
    foundExcel = False

    ' Check through the selected references in the References dialog box.
    For Each chkRef In References


        ' If the reference is broken, send the name to the Immediate Window.
        If chkRef.IsBroken Then
           Debug.Print chkRef.Name
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD9.olb")) <> 0 Then
            foundWord = True
        End If

        If InStr(UCase(chkRef.FullPath), UCase("EXCEL9.OLB")) <> 0 Then
            foundExcel = True
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD.olb")) <> 0 Then
            References.Remove chkRef
        ElseIf InStr(UCase(chkRef.FullPath), UCase("EXCEL.EXE")) <> 0 Then
            References.Remove chkRef
        End If


    Next

    If (foundWord = False) Then
        References.AddFromFile ("\pathto\database\MSWORD9.OLB")
    End If

    If (foundExcel = False) Then
        References.AddFromFile ("\pathto\database\EXCEL9.OLB")
    End If

End Sub

回答by dwo

Here is a code sample, which checks for broken references. I know this is not the whole solution for you, but it will give you some clues how to do it.

这是一个代码示例，用于检查损坏的引用。我知道这不是您的完整解决方案，但它会给您一些如何去做的线索。

Public Function CheckRefs()
    On Error GoTo Handler

    Dim rs As Recordset
    Dim ref As Reference
    Dim msg As String

    For Each ref In Application.References
        ' Check IsBroken property.
        If ref.IsBroken = True Then
            msg = msg & "Name: " & ref.Name & vbTab
            msg = msg & "FullPath: " & ref.FullPath & vbTab
            msg = msg & "Version: " & ref.Major & "." & ref.Minor & vbCrLf
        End If
    Next ref

    If Len(msg) > 0 Then MsgBox msg
    Exit Function

Handler:
    ' error codes 3075 and 3085 need special handling

    If Err.Number = 3075 Or Err.Number = 3085 Then
        Err.Clear
        FixUpRefs
    Else
        rs.Close
        Set rs = Nothing
    End If
End Function

Private Sub FixUpRefs()
    Dim r As Reference, r1 As Reference
    Dim s As String

    ' search the first ref which isn't Access or VBA
    For Each r In Application.References
        If r.Name <> "Access" And r.Name <> "VBA" Then
            Set r1 = r
            Exit For
        End If
    Next
    s = r1.FullPath

    ' remove the reference and add it again from file
    References.Remove r1
    References.AddFromFile s

    ' hidden syscmd to compile the db
    Call SysCmd(504, 16483)
End Sub