Moving a Reference from One Computer to another

spurs

Active Member
Joined
Oct 18, 2006
Messages
435
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

spurs

Active Member
Joined
Oct 18, 2006
Messages
435

ADVERTISEMENT

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

Active Member
Joined
Oct 18, 2006
Messages
435
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

Active Member
Joined
Oct 18, 2006
Messages
435

ADVERTISEMENT

bump
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Active Member
Joined
Oct 18, 2006
Messages
435
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,562
Messages
5,659,558
Members
418,507
Latest member
Jsejms

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top