Setting references code problem

[Wylie C]

I developed an excel project in 2000 and am now trying to deploy the project
in 2003 version of MS Office. Below is the code that has been developed. An
error occurs at line 4 and jumps to the error handler. Errormessage #1004 is
then displayed. As a note, I tried dimming ref as an object and also a
reference and an error occured with both. What is wrong with this code. Thank
you.

Public Sub ProjAddRef()
On Error GoTo Handle
Dim ref As Variant 'variant for ref only type that works with ref
collection
For Each ref In Application.VBE.ActiveVBProject.References 'iterate through
collection
With ref 'this will fix any broken references in project
If
Application.VBE.ActiveVBProject.References.Item("MSForms").IsBroken = True
Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{0D452EE1-E08F-101A-852E-02608C4D0BB4}",
2, 0) = True 'ms forms reference C:\WINNT\system32\FM20.DLL full path
End If
If
Application.VBE.ActiveVBProject.References.Item("Access").IsBroken = True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}",
9, 0) = True 'access ref C:\Program Files\Microsoft
Office\Office\MSACC9.OLB full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADOR").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000300-0000-0010-8000-00AA006D2EA4}",
2, 6) = True 'ador ref C:\Program Files\Common
Files\System\ADO\msador15.dll full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADOX").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000600-0000-0010-8000-00AA006D2EA4}",
2, 6) = True 'adox ref C:\Program Files\Common Files\System\ADO\msadox.dll
full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADODB").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000205-0000-0010-8000-00AA006D2EA4}",
2, 5) = True 'adodb ref C:\Program Files\Common
Files\System\ADO\msado25.tlb full path
End If
If Application.VBE.ActiveVBProject.References.Item("JRO").IsBroken =
True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{AC3B8B4C-B6CA-11D1-9F31-00C04FC29D52}",
2, 6) = True 'jro ref C:\Program Files\Common Files\System\ADO\msjro.dll
full path
End If
If
Application.VBE.ActiveVBProject.References.Item("Shell32").IsBroken = True
Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{50A7E9B0-70EF-11D1-B75A-00A0C90564FE}",
1, 0) = True 'shell32 ref C:\WINNT\system32\shell32.dll full path
End If
If Application.VBE.ActiveVBProject.References.Item("VBIDE").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{0002E157-0000-0000-C000-000000000046}",
5, 3) = True 'vbide ref C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB full path
End If
If
Application.VBE.ActiveVBProject.References.Item("SHAPPMGRLib").IsBroken =
True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{3964D990-AC96-11D1-9851-00C04FD91972}",
1, 0) = True 'shapmgrlib ref C:\WINNT\System32\appwiz.cpl full path
End If
Debug.Print .Name & " " & "Name" '''these print to
debug window
Debug.Print .Major & " " & "Major"
Debug.Print .Minor & " " & "Minor"
Debug.Print .BuiltIn & " " & "built in"
Debug.Print .Guid & " " & "Guid"
Debug.Print .FullPath & " " & "full path"
Debug.Print .IsBroken & " " & "is broken"
Debug.Print "***********************************************"
End With
Next
Exit Sub
Handle:
Select Case Err.Number
Case 32813
Resume Next 'if reference already exists
Case Else
MsgBox Err.Number & vbNewLine & Err.Description
End Select
End Sub

 

[Wylie C]

I forgot to mention that the code runs without error in the office 2000
environment and the error only occurs in the 2003 verions of Excel.

 

[Dave Peterson]

Based on your followup post, maybe it's a security measure that was added in
xl2002.

Tools|macro|security|Trusted Publishers tab
Check "Trust access to visual basic project"

This is a user setting -- you can't change it in your code. Each user will have
to decide if they want to allow access to the project.

===
I didn't take the time to test this, though.