Hi,

I have distributed a spreadsheet which has a reference set to the
Microsoft ActiveX Data Objects 2.8 library. Unfortunately we have now
discovered that "some" users have this reference MISSING.

I wanted to send out a program to open the VB module of the workbook
with the missing reference, remove the MISSING reference and add a new
reference to the ActiveX Data Objects library that they do have
(2.5).

Problem 1.

I have been trying to test some code in my own copy of the sheet to
add references as per below;

Dim varaddreference
varaddreference =
ActiveWorkbook.VBProject.References.AddFromGuid("{ EAB22AC0-30C1-11CF-
A7EB-0000C05BAE0B}", 1, 1)

However I get an error;

Run time error 438
Object doesn't support this property or method

Problem 2.

I do not know how to remove references that are marked as missing

Problem 3.
If I can get this code working how can i get the GUID for a reference
I do not have on my PC (for the ActiveX Data Objects 2.5 library)?

Assuming that the other machine does indeed have ActiveX Data Objects
installed, you can use code like the following.

Sub Auto_Open()
On Error Resume Next
Dim Ref As Object
On Error Resume Next
With ThisWorkbook.VBProject.References
Set Ref = .Item("ADODB")
If Not Ref Is Nothing Then
.Remove Ref
End If
.AddFromGuid "{2A75196C-D9EB-4129-B803-931327F72D5C}", 0, 0
End With

With Application.VBE.CommandBars.FindControl(ID:=578)
.Execute
.Execute ' yes, twice -- required by earlier versions of Excel.
End With
End Sub

This code should go in its own module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"keri" wrote in message
oups.com...
Hi,

I have distributed a spreadsheet which has a reference set to the
Microsoft ActiveX Data Objects 2.8 library. Unfortunately we have now
discovered that "some" users have this reference MISSING.

I wanted to send out a program to open the VB module of the workbook
with the missing reference, remove the MISSING reference and add a new
reference to the ActiveX Data Objects library that they do have
(2.5).

Problem 1.

I have been trying to test some code in my own copy of the sheet to
add references as per below;

Dim varaddreference
varaddreference =
ActiveWorkbook.VBProject.References.AddFromGuid("{ EAB22AC0-30C1-11CF-
A7EB-0000C05BAE0B}", 1, 1)

However I get an error;

Run time error 438
Object doesn't support this property or method

Problem 2.

I do not know how to remove references that are marked as missing

Problem 3.
If I can get this code working how can i get the GUID for a reference
I do not have on my PC (for the ActiveX Data Objects 2.5 library)?

I have placed this code in a blank workbook without any reference to
ActiveX Data Objects set and tried it, however nothing happens when I
run the code.
I cannot see why as I read the code to be -
If the adodb reference exists, remove it.
Whether the adodb reference existed initally or not, set a new
reference to the one listed.

(I have also tried running this as .addfromfile and this does not work
either.

If I run the code with a reference set it does not get rid of it.



I was thinking of trying code like this below;

sub
'code to check if current reference is missing
if (code above) = true then
ActiveWorkbook.VBProject.References.remove.item
ActiveWorkbook.VBProject.References ("adobb")
ActiveWorkbook.VBProject.References.AddFromFile ("c:\program files
\common files\system\ado\msado20.tlb")
end if
end sub

however where I can get this to work when testing on my machine I
cannot get it to work on any other machine.
Please help, I need a fix for this by the morning and I am almost bald