Adding references to Libraries

[reneemettrie]

In the VBA editor you can go to Tools-References and add a reference to, for instance, the Excel library.
However, if you open your file on a computer with a different version of Excel, you will see that some libraries are missing and the one you need is not in the list. Can you solve this problem without changing it manually?
I read you can add the reference in your VBA code but I do not know how to do that? The code should be able to detect the excel version of the current computer.

 

[Banana]

Some keywords to use in Access help:

BrokenReferences
IsMissing
Reference Object
References Collection

HTH.

 

[reneemettrie]

Some keywords to use in Access help:

BrokenReferences
IsMissing
Reference Object
References Collection

HTH.

OK, you can use
References.AddFromFile Filename:="C:\Program Files\Microsoft Office 2007\Office12\EXCEL.EXE"

But what if you do not know the version or the location of excel on the other computer?

 

[Banana]

For references to files provided by Microsoft, I would expect them to be always in the same location. Else, the whole Office installation wouldn't work reliably, and it would be better to simply repair the installation than trying to fix it yourself by copying the file along with the front-end.

I would be more concerned if the library I wanted to reference was a custom library, where extra steps is necessary to ensure that the library is present.

As for the versions, you would have to set a minimum threshold for which you want to support (let's say Excel 97) because that is what your code uses, and can run without breaking (this is especially important if you happen to use something that is available only in 2003, but not in prior versions), then do a loop:

'Pseudocode

i=12

Do until FoundAReference
     select case i
         case 12
           addfile pathtoOffice12
         case 11
            addfile pathtoOffice11
         case else
            msgbox "Need Office 11 or greater to run"
    end select
    i=i-1
loop