Is it possible to set project references at run-time? RRS feed

  • Question

  • Hi

    I'm developing an application based on Word and Access (version 2007 and later).

    I compiled the Word Template and the Access database in Office 2013.
    When I install in Office 2010 the following problems with references occur.
      • Missing  or not selected in the Word Project. :

        • Microsoft Forms 2.0 Object Library,

        • Microsoft Scripting Runtime

        • Microsoft Access 14.0 Object Library

      • Missing in Access
      • Microsoft Word 14.0 Object Library   

    Is it possible to set references conditionally based on which version of Office the user is using?

    Thnks, Lauro

    Sunday, July 24, 2016 11:55 AM

All replies

  • Basically you need to compile it in Office 2007 and then use it in Office 2007/2010/2013/2016.  The references should update appropriately.  This is one reason I insist on a minimum version of Office (2010) since I often reference Word from Excel or Excel from Word.  I have had a few problems with Office 2007.  You may need to make the minimum version 2010.

    You can use late binding to avoid this problem.  I find late binding harder to write app but it avoids the reference problem.
    Sunday, July 24, 2016 12:55 PM
  • I've given up handling different versions with early binding. Clients were running and saving in a later version then opening and running in an older version so it failed again.

    Instead I develop in early binding mode. I specify what the earliest version is, so if its 2010 I have to make sure I don't use any 2013 or 2016 features.

    Once the code works, I convert to late binding, but using conditional compilation. Example below is for Project controlling Excel, but the same technique works exactly the same for Excel to Word or Access.

    Option Explicit #Const xlDebug = False '#Const xlDebug = True Sub Test() 'The #If syntax provides conditional compilation. #If xlDebug Then Dim xlApp As Excel.Application Dim ReportBook As Excel.WorkBook Dim ReportSheet As Excel.Worksheet Dim xlr As Excel.Range Dim WrkSht As Excel.Worksheet #Else Dim xlApp As Object Dim ReportBook As Object Dim ReportSheet As Object Dim xlr As Object Dim WrkSht As Object
    'With no reference, all Excel constants need defining as well
    'I keep them in as it makes the code easier to read, understand and debug. Const xlAscending = 1 Const xlDateOrder = 32 Const xlYes = 1 Const xlPivotTableVersion12 = 3 Const xlDatabase = 1 Const xlSum = -4157 #End If 'All other code here End Sub

    Comment out one of the Const xldebug lines and the other holds true. If you set xldebug to true you need an Excel reference. For the client I set xldebug to false and remove the Excel Reference and the code runs automatically in late binding mode. I haven't found any speed differences and this has worked for a number of applications.

    This technique gives me the best of both worlds, quick and easy development and testing and easier deployment.

    Happy programming!

    Rod Gill
    Author of the one and only Project VBA Book

    Sunday, July 24, 2016 9:01 PM
  • hi,

    I was thinking to follow your suggestion but the I realized that

    #Const xlDebug = False
    '#Const xlDebug = True

    has to be declared at module level; and I have more then 20 modules and more or less User Forms.

    It will not be very easy to  comment an uncomment the consts in so many places, and not in only one place as i was thinking.

    Ciao, Lauro

    Sunday, July 31, 2016 5:41 PM
  • I usually have all code talking to Excel in one module, but if that doesn't work well, this method is still the most robust for production purposes. Sure you will have one or two problems when you forget to set a debug constant, but once resolved I've found it the most reliable way and the way that makes support and future code edits easiest and quickest.

    Happy programming

    Rod Gill
    Author of the one and only Project VBA Book

    Sunday, July 31, 2016 7:26 PM