Hello guys!
I'm having a problem when moving an xlsm file into another computer with a missing reference.
After a little research I've found that the problem is the minor number.
For some reason, in my PC the reference is added with minor = 2 and the target PC needs it with minor=0 (I've checked the minor number in the target PC by fixing the reference by hand and listing the references).
First of all, why's that difference with minor numbers? The workbook use 8 different references and only one fails to load because of that.
I've tried to fix this with some code I found on the net and I can't remove the reference!
Here's the code:
I've run the code in debug mode and stoped after the Remove line is executed, went to see if the reference was actually removed but it's still there so I guess that's why it isn't added again later (because it's already there).
What am I missing?
I'm having a problem when moving an xlsm file into another computer with a missing reference.
After a little research I've found that the problem is the minor number.
For some reason, in my PC the reference is added with minor = 2 and the target PC needs it with minor=0 (I've checked the minor number in the target PC by fixing the reference by hand and listing the references).
First of all, why's that difference with minor numbers? The workbook use 8 different references and only one fails to load because of that.
I've tried to fix this with some code I found on the net and I can't remove the reference!
Here's the code:
Code:
Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long
'Update the GUID you need below.
strGUID = "{00024517-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
[B]ThisWorkbook.VBProject.References.Remove theRef[/B]
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub
I've run the code in debug mode and stoped after the Remove line is executed, went to see if the reference was actually removed but it's still there so I guess that's why it isn't added again later (because it's already there).
What am I missing?