Moving a Reference from One Computer to another

[spurs]

I have vba code which has a specific reference called OrbitCom library which must be installed on the computer in order for the vba code to work.

The references dialogue box tells me that the required file is:
C:\Windows\system32\OrbitCOM.dll

When I try to run the workbook on another computer that also has the same OrbitCom library installed on it get an error in the references
Missing OrbitCom Library

I have to remove the reference and then close references, then reopen the references dialogue box and reclick on OrbitCOM library which has the identical path and filename that could not be found before ie
C:\Windows\system32\OrbitCOM.dll


Can this error be avoided somehow?
or
Is there a way to run another macro to fix this error that can be sent out to all of those using this workbook. No one has the knowledge to manually fix this so it is a big problem.

 

[spurs]

bump

 

[Smitty]

Ken Puls has a neat method for programmatically adding references here.

HTH,

 

[spurs]

This worked great thank you

 

[spurs]

Smitty

As an extension to the problem

If i wanted to remove a specific reference
GUID:="{158DE013-BF77-4150-B401-D22294A0C5AD}"

what syntax would be needed?

I tried
ThisWorkbook.VBProject.References.Remove GUID:="{158DE013-BF77-4150-B401-D22294A0C5AD}", Major:=1, Minor:=0

but that doesnt work -

 

[spurs]

Smitty
I was a bit hasty in saying it worked.

Here is what I found.


I used one of the programs to figure out which references were being used in the project. The list is below

Reference name.....Full path to reference.....Reference GUID
VBA .......C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL.....{000204EF-0000-0000-C000-000000000046}
Excel ......C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE.....{00020813-0000-0000-C000-000000000046}
stdole.....C:\Windows\system32\stdole2.tlb.....{00020430-0000-0000-C000-000000000046}
MSForms.....C:\Windows\system32\FM20.DLL.....{0D452EE1-E08F-101A-852E-02608C4D0BB4}
OrbitCOM.....C:\Windows\system32\OrbitCOM.dll.....{158DE013-BF77-4150-B401-D22294A0C5AD}
NETCommOCX.....c:\Program Files\Project1\NETComm.ocx.....{4580EBBB-FE3D-45CF-8543-600A62B38A73}

I then moved the program to another computer that had all of the references installed. When I ran the same program to see if the software picks up the references i got

Reference name.....Full path to reference.....Reference GUID
VBA .......C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL.....{000204EF-0000-0000-C000-000000000046}
Excel ......C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE.....{00020813-0000-0000-C000-000000000046}
stdole.....C:\Windows\system32\stdole2.tlb.....{00020430-0000-0000-C000-000000000046}
MSForms.....C:\Windows\system32\FM20.DLL.....{0D452EE1-E08F-101A-852E-02608C4D0BB4}
.............................................................{158DE013-BF77-4150-B401-D22294A0C5AD}
NETCommOCX.....c:\Program Files\Project1\NETComm.ocx.....{4580EBBB-FE3D-45CF-8543-600A62B38A73}

Notice that the Reference Name and Full Path for OrbitCOM is missing but the GUID is there.

I then ran the following program to try to remove the reference OrbitCOM bad reference. This program came from the souce you directed me to - ie Ken Puls


Sub RemoveLostReferences()
'Macro purpose: To remove lost references on the project using the GUID for the
'reference library

'*************************************************************************************
' Note: for this macro to work
'the TRUST ACCESS TO VISUAL BASIC PROJECT box MUST be checked, or the code will not work.
'(This box is located in Tools|Options|Security|Macro Security|Trusted Publishers
'**********************************************************************************

Dim strGUID(6), A As String, theRef As Variant, i As Long


'Set to continue in case of error
On Error Resume Next

'Remove any missing GUID references
For i = ThisWorkbook.VBProject.References.count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
On Error GoTo 0
End Sub

When stepping through this program, the If loop does recognize that the OrbitCOM reference is broken and the line

ThisWorkbook.VBProject.References.Remove theRef

is executed but it does not remove the bad reference.

Any ideas on how to remove this reference?

 

[spurs]

bump

 

[tusharm]

My approach would be to *not* rely on the reference (something that is called early binding) and instead use late binding.

See http://www.pcreview.co.uk/forums/thread-969149.php

I have vba code which has a specific reference called OrbitCom library which must be installed on the computer in order for the vba code to work.

The references dialogue box tells me that the required file is:
C:\Windows\system32\OrbitCOM.dll

When I try to run the workbook on another computer that also has the same OrbitCom library installed on it get an error in the references
Missing OrbitCom Library

I have to remove the reference and then close references, then reopen the references dialogue box and reclick on OrbitCOM library which has the identical path and filename that could not be found before ie
C:\Windows\system32\OrbitCOM.dll


Can this error be avoided somehow?
or
Is there a way to run another macro to fix this error that can be sent out to all of those using this workbook. No one has the knowledge to manually fix this so it is a big problem.

 

[spurs]

Tusharm
I dont care about early or late binding - as long as it gets the job done:
The job is to get rid of the bad reference
Missing Reference OrbitCom Library and link up the correct library OrbitCom Library

How would you suggest this be accomplished code wise?

 

[tusharm]

I wouldn't try and connect to the correct library. I would write a solution that doesn't require the reference to exist in the first place. And, that is called late binding.

See my posts in the link I shared in my last post. I share how I use references (aka early binding) to help development and then don't use references for smooth distribution of the software.

Tusharm
I dont care about early or late binding - as long as it gets the job done:
The job is to get rid of the bad reference
Missing Reference OrbitCom Library and link up the correct library OrbitCom Library

How would you suggest this be accomplished code wise?