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

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" <leavemealone@home wrote in message
...
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

Thanks Ivan, that's exactly what I needed.

Fred

"Ivan Raiminius" wrote in message
oups.com...
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

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

--
Regards,
Tom Ogilvy


"Fred" wrote:

Thanks Ivan, that's exactly what I needed.

Fred

"Ivan Raiminius" wrote in message
oups.com...
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

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

Ivan Raiminius wrote:
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

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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote:

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" <leavemealone@home wrote in message
...
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

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.

--
Regards,
Tom Ogilvy



"Ivan Raiminius" wrote:

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

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

RBS

"Tom Ogilvy" wrote in message
...
Seems to me you would only set a version if your code required that or a
newer to work, then test for a failure.

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote:

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" <leavemealone@home wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
Not sure what you are precisely saying.
Would you alter the posted code?

RBS

"Tom Ogilvy" wrote in message
...
Seems to me you would only set a version if your code required that or a
newer to work, then test for a failure.

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote:

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" <leavemealone@home wrote in message
...
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

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

RBS

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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
Not sure what you are precisely saying.
Would you alter the posted code?

RBS

"Tom Ogilvy" wrote in message
...
Seems to me you would only set a version if your code required that or
a
newer to work, then test for a failure.

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote:

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" <leavemealone@home wrote in message
...
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