Adding a reference programatically

[Fred]

I create a new workbook simply by copying a worksheet, i.e.
ActiveSheet.copy.
The problen is that the sheet has subroutines that require the ADODB and so
the new workbook needs to reference the Microsoft ActiveX Data Objects
Library.
I know I can add that reference to the new workbook using Tools:References
from the main menu but how can I add that reference to the new workbook
programmatically.

Thanks for any help,
Fred

 

[Ivan Raiminius]

Hi Fred,

in tools >> references check Microsoft Visual Basic Extensibility

and then in object browser explore new library VBIDE.

If anything is not clear, please let me know.

Regards,
Ivan

 

[RB Smissaert]

Sub AddADO()

Dim R

For Each R In ThisWorkbook.VBProject.References
If R.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And R.Major = 2
Then
Exit Sub
End If
Next

On Error GoTo NOTFOUND

'although usually the ADO version will be higher, doing Minor:=0 will
install
'the higher version if available. On the other hand when you specify
Minor:=5
'and only a lower version is available, this can't be installed
'----------------------------------------------------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
Major:=2, Minor:=0
Exit Sub

NOTFOUND:
On Error GoTo 0

End Sub


RBS

 

[Fred]

Thanks Ivan, that's exactly what I needed.

Fred

 

[Guest]

It would be better to design your code to use late binding, then you won't
need the reference.

 

[Ivan Raiminius]

Hi Fred,

Tom is right, if you use late binding, you don't need the reference to
VBIDE. Early binding and late binding both have it's dis/advantages.

If you use early binding it's easier to write the code (you take
benefit from intellisense), and you can later easily rewrite the code
for late binding - this is what I would suggest to you.

Anyway, if you are not familiar with any library, it is much more
comfortable to write your code using early binding.

If you plan to use your code in multiple environments it's safe to use
late binding - you can have different versions of libraries on each
computer and I simply don't believe that excel is clever enough to
choose the right one (although it should be able to choose the same
version or newer).

Regards,
Ivan

 

[bart.smissaert]

Hi Fred,

Tom is right, if you use late binding, you don't need the reference to
VBIDE. Early binding and late binding both have it's dis/advantages.

If you use early binding it's easier to write the code (you take
benefit from intellisense), and you can later easily rewrite the code
for late binding - this is what I would suggest to you.

Anyway, if you are not familiar with any library, it is much more
comfortable to write your code using early binding.

If you plan to use your code in multiple environments it's safe to use
late binding - you can have different versions of libraries on each
computer and I simply don't believe that excel is clever enough to
choose the right one (although it should be able to choose the same
version or newer).

Regards,
Ivan

I don't think there is any problem with setting a reference to the ADO
library.
Just save your workbook without the reference and have code that adds
the
reference on opening the workbook as in the code I posted earlier.
Never have seen a problem with it.

RBS

 

[Guest]

Seems to me you would only set a version if your code required that or a
newer to work, then test for a failure.

 

[Guest]

The reference will be set to the version specified in the registry. Only
one version will be tied to that GUID (Bart's method). The last version
installed/registered is the one pointed to by the registry.

 

[RB Smissaert]

Not sure what you are precisely saying.
Would you alter the posted code?

RBS

 

[Tom Ogilvy]

I was saying why not set both major and minor to zero unless your code
requires a certain version (or newer).

 

[RB Smissaert]

Yes, could do that, but found that Major:=2 is always there.

RBS