Can't remove missing reference using VBA

[BeRniTo]

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:

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?

 

[jaryszek]

Hi,

is someone who done it?

Jacek

 

[RoryA]

I have to admit I have never understood why anyone would attempt this when late binding code is generally far simpler and more reliable.

 

[jaryszek]

In OWC 11 (windows web components ) you can not use lata binding.
So there is a very huge problem.

I am trying to do the same in Access but in internet there is small information about it.

Best Wishes,
Jacek

 

[RoryA]

I would think you can use late binding as long as you add the controls at runtime. If they are already present on a form, you'll get errors before your code even runs. Given that they were discontinued from Office 2003 (though available up to 2010 I think), I wouldn't recommend using them anyway.

 

[jaryszek]

You can't do late binding with OWC11.dll. I checked it and microsoft said that

This is a great future if you can choose all fields and create the report.
But i additionally added also pivot table from Excel binded with Access - more safety way,

Jacek

 

[RoryA]

You can't do late binding with OWC11.dll. I checked it and microsoft said that

You shouldn't believe everything Microsoft say.

 

[jaryszek]

If you know the solution - i will gladly find oout

Anyway,
thank you!

Best Wishes,
Jacek

 

[RoryA]

As I said, you would need to add the controls at runtime, so that you can then handle any errors from that, which would be due to the library not being present. I can't be more specific as I don't know what you are doing with the OWC.

 

[jaryszek]

Yes i added controls (error handlings) and if there is no reference to OWC i am adding it form file.

Topic is here:
http://www.accessforums.net/showthread.php?t=68192