vba 动态设置对象库引用可能吗？

Setting Object Library References Dynamically possible?

提问by Justin

So I have an MS Access application originally in a 2002-2003 format. Developed on a production network image, and when the image pushed the office upgrade, it became office 2007. So this is used in a very non traditional way...reps take it with them on company laptops enter data, and it automates complex ppt presentations, and excel worksheet for them (they love it....they need it). Data is not held in this like a database repository, just long enough to produce the automation they need and it works....saves them time. So Access is limited, and in the use case, really limited since i am using it in a way it certainly wasn't intended to be used. I get that, but like I said so far, its working pretty well.....

所以我有一个最初采用 2002-2003 格式的 MS Access 应用程序。在生产网络镜像上开发，当镜像推动 office 升级时，它变成了 office 2007。演示文稿，并为他们准备了 excel 工作表（他们喜欢它......他们需要它）。数据不像数据库存储库那样保存在这个存储库中，只要足够长的时间来产生他们需要的自动化并且它可以工作......为他们节省时间。所以访问是有限的，在用例中，真的很有限，因为我以一种肯定不打算使用的方式使用它。我明白了，但就像我到目前为止所说的，它工作得很好......

The one bump I have run into is if someone has to use another computer for whatever reason (doesn't happen often but it got me thinking), and say they have a version of 2002-2003 access, the tool will run, but once we get to the code routines/modules for ppt & outlook, the object libraries show MISSING because working on this tool seems to automatically cause the libraries to go up to the next available version, but not down to find the version.....

我遇到的一个问题是，如果有人出于某种原因不得不使用另一台计算机（这种情况并不经常发生，但让我想到了），并说他们有一个 2002-2003 版本的访问权限，该工具将运行，但是一旦我们进入 ppt 和 Outlook 的代码例程/模块，对象库显示 MISSING，因为使用此工具似乎会自动导致库升级到下一个可用版本，但不会向下查找版本.....

so originally when I made this....it used MS PowerPoint 11.0, and MS Outlook 11.0, and then when I had to start working in 2007 it became 12.0 for both, some instances I see it bumped up to 14.0, and none of the ups are a problem, but now since I have an image with Office 2007 out, and new files version I try to give them has the libraries defaulted to 12.0 and if they run into a scenario where they would take the file on a disc and use it on a computer that has office 2003, those libraries just come up missing and it doesn't recognize the appropriate 11.0 object libraries anymore.

所以最初当我做这个时......它使用了 MS PowerPoint 11.0 和 MS Outlook 11.0，然后当我不得不在 2007 年开始工作时，两者都变成了 12.0，在某些情况下我看到它上升到 14.0，但没有一个ups 是一个问题，但现在因为我有一个带有 Office 2007 的图像，并且我尝试给他们的新文件版本将库默认为 12.0，如果他们遇到他们将文件放在光盘上的情况，在装有 office 2003 的计算机上使用它，这些库刚刚丢失，并且不再识别适当的 11.0 对象库。

So finally my question....any suggesions (save the obvious and build a real app...lol...would love to....no $) to handling this? Is there code that can/will evaluate what libraries need to be set on opening the file using vba? Is that possible?

所以最后我的问题......任何建议（保存明显的并构建一个真正的应用程序......大声笑......会喜欢......没有$）来处理这个问题？是否有代码可以/将评估在使用 vba 打开文件时需要设置哪些库？那可能吗？

回答by HansUp

There is Remove Methodwhich you could use to remove a reference, then AddFromFile Methodor AddFromGuid Methodto add a reference.

有remove方法，你可以用它来删除一个引用，则AddFromFile方法或AddFromGuid方法添加一个参考。

The AddFromFile approach was offered in answers to this recent StackOverflow question: Add references programaticallyHowever, make sure to read Tony's answer... if you're distributing your application as an MDE, you're SOL.

最近 StackOverflow 问题的答案中提供了 AddFromFile 方法：以编程方式添加引用但是，请务必阅读Tony 的回答……如果您将应用程序作为 MDE 分发，那么您就是 SOL。

Consider switching to late binding for your PowerPoint and Outlook stuff. That way you won't have to fiddle with references (no reference required for late binding). A drawback from late binding is you lose the benefit of IntelliSense during development. However, you can develop with early binding, then switch to late binding for deployment. Substitute the values where you used any named constants from the referenced library ... debug will highlight any you overlook.

考虑为您的 PowerPoint 和 Outlook 内容切换到后期绑定。这样你就不必摆弄引用（后期绑定不需要引用）。后期绑定的一个缺点是您在开发过程中失去了 IntelliSense 的优势。但是，您可以使用早期绑定进行开发，然后切换到后期绑定进行部署。替换您使用引用库中任何命名常量的值......调试将突出显示您忽略的任何内容。

A frequent counter-argument is that late binding imposes a "performance penalty". However, I've yet to encounter a use case where the "slow-down" was large enough to be noticeable by a human observer.

一个常见的反驳意见是后期绑定会带来“性能损失”。但是，我还没有遇到过“减速”大到足以被人类观察者注意到的用例。

In any case I've been using late binding for years specifically to avoid the type of deployment issue you're struggling with now.

无论如何，我多年来一直在使用后期绑定，专门用于避免您现在正在努力解决的部署问题类型。

Edit: Here is an example which uses the Excel type library. I have it set to use early binding. To convert it to late binding, comment out the declarations under '* early binding ..., and uncomment those under '* late binding ...

编辑：这是一个使用 Excel 类型库的示例。我已将其设置为使用早期绑定。要将其转换为后期绑定，请注释掉'* early binding ...下的声明，并取消注释'* late binding ...下的那些。

Also notice the 2 lines which include the named constant, xlAutomatic.

还要注意包含命名常量 xlAutomatic 的 2 行。

Public Sub EarlyVsLateBinding()
    Dim strFullPath As String
    '* early binding requires reference to Excel type library *'
    Dim objExcel As Excel.Application
    Set objExcel = New Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    '* late binding; no reference required *'
'    Dim objExcel As Object '
'    Dim objBook As Object '
'    Dim objSheet As Object '
'    Set objExcel = CreateObject("Excel.Application") '

    strFullPath = CurrentProject.Path & Chr(92) & "foo.xls"

    Set objBook = objExcel.Workbooks.Open(strFullPath)
    Set objSheet = objBook.Worksheets("bar")
    objSheet.Range("B1").Select
    With objExcel.Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .ColorIndex = xlAutomatic 'named constant available with reference set '
        '.ColorIndex = -4105 'substitute xlAutomatic value so we drop reference
    End With
    objBook.Close SaveChanges:=True
    objExcel.Quit
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objExcel = Nothing
End Sub