PC Review Forums Newsgroups Microsoft Excel Microsoft Excel Programming Setting references code problem

Reply

Setting references code problem
 
Thread Tools Rate Thread
Old 02-11-2005, 05:18 PM   #1
=?Utf-8?B?V3lsaWUgQw==?=
Guest
 
Posts: n/a
Default Setting references code problem


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
  Reply With Quote
Old 02-11-2005, 05:26 PM   #2
=?Utf-8?B?V3lsaWUgQw==?=
Guest
 
Posts: n/a
Default I forgot to mention..
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.

"Wylie C" wrote:

> 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
>
>
  Reply With Quote
Old 02-11-2005, 07:39 PM   #3
Dave Peterson
Guest
 
Posts: n/a
Default Re: Setting references code problem
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.

Wylie C wrote:
>
> 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

--

Dave Peterson
  Reply With Quote
Reply

Archive


Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off