Moving a Reference from One Computer to another

spurs

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

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

spurs

Active Member
Joined
Oct 18, 2006
Messages
430

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
430
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
430

ADVERTISEMENT

bump
 

tusharm

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,403
Messages
5,511,154
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top