References

[keri]

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)?

 

[Chip Pearson]

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]

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