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?

 

[RoryA]

When I say controls, I don't mean error handling, I mean the actual controls - the Spreadsheet, PivotTable, PivotChart objects.

 

[jaryszek]

Hmm thank you RoryA.

my code looks like :

Private Sub Polecenie11_Click()

Dim fst1 As Object
Dim frm1, frm2 As Object
Dim i As Long
Dim rst As DAO.Recordset
Dim coll As New Collection
Dim coll2 As New Collection
Dim FieldSety As Variant
Dim FieldSetyPivot As Variant


On Error GoTo Koniec


'Open form in PivotTable view and set
'a reference to the form
Set frm2 = Forms.Item("tbl_Name_PivotForm")




Set rst = frm2.Recordset


''Me.PivotTable.ActiveView.DataAxis.FieldSets(0).Fields(0).DetailBackColor = RGB(155, 155, 155)


'Set PivotTable fieldsets


rst.MoveLast
rst.MoveFirst


Do While Not rst.EOF
    If IsChecked(frm2!Wybór) = True Then
        coll.Add frm2!Nazwa_Pola.Value, frm2!Nazwa_Pola.Value
    Else
        coll2.Add frm2!Nazwa_Pola.Value, frm2!Nazwa_Pola.Value
    End If
rst.MoveNext
Loop


If coll.Count = 0 Then
    MsgBox "Wybierz chociaż jedno pole do tabeli przestawnej"
    Exit Sub
End If


DoCmd.OpenForm "BrakiForm_Pivot", acFormPivotTable
Set frm1 = Forms.Item("BrakiForm_Pivot")


For Each FieldSetyPivot In frm1.PivotTable.ActiveView.DataAxis.FieldSets
   Set fst1 = frm1.PivotTable.ActiveView.FieldSets(0)
   frm1.PivotTable.ActiveView.DataAxis.RemoveFieldSet FieldSety
Next FieldSetyPivot


For Each FieldSety In coll
    Set fst1 = frm1.PivotTable.ActiveView.FieldSets(FieldSety)
    frm1.PivotTable.ActiveView.DataAxis.InsertFieldSet fst1
    ''frm1.PivotTable.ActiveView.DeleteFieldSet fst1
Next FieldSety






''Call ClearPivot(coll, frm1)


DoCmd.Close acForm, Me.Name


Exit Sub
Koniec:
MsgBox "Błąd danych, skontaktuj się z administratorem " & Err.Number & Err.Description

I am reference to form object and pivottable method of this object...

What do you think about that ?

Best Wishes,
Jacek

 

[jaryszek]

Okey i think that is my mistakes.

Here in my code i dont refere to OWC11 componenets.
I used another code and mistakenly i did not remove it from my application.
And because of it the error was appearing...

Sorry for confusing you my friends,
Best wishes,
Jacek