Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Broken References (VBA/Word/2000)

    Does anyone know how to remove a broken reference from a Word 2000 project? Here broken means when viewed through the References dialog box, the reference I am trying to remove will be preceded by the word MISSING:

    The following code does not work. Word gives an error about "Object Library not registered" (which is the whole point) when the remove method is invoked.
    Only a few properties will work if the reference is broken, GUID and type
    but not name, description or fullpath

    With oDoc
    '***First remove the bad references
    For Each oRef In .VBProject.References
    If oRef.IsBroken Then
    .VBProject.References.Remove oRef 'Trips error
    End If
    Next oRef
    End With

    Thanks

    Vick

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Removing Broken References (VBA/Word/2000)

    How many times have you said to yourself "this should work!!"?

    If you compare Microsoft's HOW TO: Check and Remove Incorrect Project References in the Visual Basic Editor in Microsoft Word (Q308340) you get basically the same code you are using. Hopefully someone else will have a suggestion.

    Longshot: are you on SR-1?

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    I am on Office 2000 SP2. I am not sure what is going on, but I have a feeling that the References object may be buggy. It is very simple to recreate the problem

    Create a dummy dll in visual basic.
    Create a template and reference the dll
    recompile the dll without binary compatibility on
    Check the references of the template and the word MISSING will be appended to the beginning of the now missing reference
    Try to remove it programmatically. All attempts seem to fail as the remove method keeps failing. The IsBroken method sometimes work and sometimes it does not.

    Vick

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    I was looking back on this post due to a problem I was having with the MS procedure to check and remove references. Unfortunately, at my company, we are still on SR1. Is there any workaround for those of us stuck with SR1?

    FYI When I try to run the procedure for checking broken references I get a compile error: "Can't find project or library" which is of course what I am trying to correct with the procedure.

    I've included the code I'm using below:

    Sub CheckReference()

    Dim vbProj As VBProject ' This refers to your VBA project.
    Dim chkRef As Reference ' A reference.

    ' Refer to the activedocument's VBA project.
    Set vbProj = ActiveDocument.VBProject

    ' Check through the selected references in the References dialog box.
    For Each chkRef In vbProj.References

    ' If the reference is broken, send the name to the Immediate Window.
    If chkRef.IsBroken Then
    vbProj.References.Remove chkRef
    End If

    Next

    End Sub

    And the reference is set to the Microsoft Visual Basic Extensibility Library 5.3

    Thanks!!
    Troy

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    I get this frequently, mainly because I'm always horsing around with libraries.

    After several long-winded efforts I hit on the idea of a complete rebuild of the project by making a subtle modification to http://www.payneconsulting.com VBA code cleaner.

    I have a version that (1) preserves the exported modules so I can pore over the entrails and (2) will build a completely new project from scratch.

    This also solves my problem, said to be due to corrupted graphics, but also occurring in projects without graphics, of corrupted templates, usual symptoms are "This @#$%^&*()_ has performed an illegal operation and " (the world will shut down)

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Removing Broken References (VBA/Word/2000)

    > Unfortunately, at my company, we are still on SR1. Is there any workaround for those of us stuck with SR1?

    I don't think having SR-1 is the problem, I was trying to rule out the possibility of a pre-SR1 problem. Actually, the earlier poster had SP2.

    I don't know why Microsoft's "solution" doesn't always work, and I didn't try to reproduce the problem myself.

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    I know you're interested in Word, but maybe this Excel code will help you. I've used the following code to check for MISSING references in xl97 and xl2000. It doesn't work on xl2002 (XP) though (hence the call to a function I wrote called IsExcelXP). As the comments show, I got it from a VBA book by John Green. Does you code at least detect the MISSING items? How does removing them solve the problem? After all if they are listed, then they are needed for the project to run so if you just remove them doesn't that still prevent the user from running your project? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    <pre>
    '------------------------------------------------------------------
    ' ExtReferencesValid() - at boot, check that required references are
    ' present on the user's sytem otherwise code will fail. Return true
    ' if all the checked references are valid, else display message.
    '
    ' Looks through list in Tools/References for items that are checked.
    '
    ' Visual Basic for Applications
    ' Microsoft Excel 9.0 Object Library
    ' Microsoft Forms 2.0 Object Library
    ' Microsoft Office 9.0 Object Library (for shapes)
    '
    ' (Stolen from Excel 2000 VBA book, John Green, page 319)
    '------------------------------------------------------------------
    Public Function ExtReferencesValid(Optional noArgs As Boolean = True) As Boolean
    Dim objRef As Object, stDescn As String

    ExtReferencesValid = True
    If IsExcelXP Then Exit Function ' this code fails in XP

    For Each objRef In ThisWorkbook.VBProject.References
    ' Debug.Print "Checking references for: " & objRef.Name
    If objRef.IsBroken Then
    'some broken links don't have descriptions, ignore
    On Error Resume Next
    stDescn = "<Not know>"
    stDescn = objRef.Description
    On Error GoTo 0

    'display message to install missing item
    MsgBox "Your copy of Excel is missing a reference to: " & _
    vbCrLf & "Name: " & stDescn & _
    vbCrLf & "Path: " & objRef.FullPath & _
    vbCrLf & _
    "Please re-install this file. This workbook will now be closed.", _
    vbCritical + vbOKOnly, "Missing Required File"
    ExtReferencesValid = False
    End If
    Next objRef

    End Function</pre>


    If anyone knows why it doesn't work in XP that'd be nice to know.

    Deb

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing Broken References (VBA/Word/2000)

    Hi Deb,
    Your code works for me on Excel 2002 as long as you have the Trust access to Visual Basic Project box checked on the Trusted Sources tab under Tools-Macro-Security, otherwise you get an error.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    Very interesting, I didn't know to look there. So how would I handle this in code since I wouldn't know if the user had that Trust checkbox set? Or are you saying that I can set Trust in my project and it'll stay set when it's opened (it's not an Application-level value) ?

    Thnx, Deb

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    Regarding: Does you code at least detect the MISSING items? How does removing them solve the problem? After all if they are listed, then they are needed for the project to run so if you just remove them doesn't that still prevent the user from running your project?

    This code does NOT detect missing references to templates that were previously attached to the document. That is what seems to be causing my problem. The code I gave above works find if I first manually remove the missing reference, but that is what I'm trying to avoid.

    To restate (since I like to complicate things beyond reason), I have a template attached to a document. However, when the document is sent externally or to someone who doesn't have the template, I want some code to run. However, one thing I must do is remove the missing reference to the missing template file. When I run the code I mentioned in my post , I get a compile error: "Can't find project or library" which is of course what I am trying to correct with the procedure.

    The code you gave skips over any references to any and all template files.

    Is there some other way to use VBA to remove the missing references to the previously attached template files?

    Thanks!!
    Troy

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    This sounds like a Catch-22 sort of deal. You can't run your program to remove references because there are currently missing references. The code I have works fine with Excel templates (tested in Excel 2000 and Excel 2002) so I don't know about Word templates. I just use it at workbook open and if it returns False that there are missing references I display a message to the user and close the workbook.

    I've never read or heard about any method to programmatically add/fix a missing reference. Many of the references have multiple .DLL files that could be chosen based on version so you'd somehow have to know which one to pick (as well as where it's located on the user's PC).

    This is always a problem when using 'extra' references (anything more than the bare-bones required ones), you can't guarantee that the end user has the file installed. This is usually when you need to use a setup.exe type of program. (Same with using add-ins, the user has to have them too.)

    I'll be interested if anyone else has a solution to this problem too. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Deb

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    I did some tests in Excel 2002 and it seems (unfortuantely) that the 'Trusted Access to VB Projects' is set at the application level and by default it is OFF which is probably what most users will have it set to. I can not programmatically set it to ON (which is reasonable since it's a security issue). Therefore, the only solution I have is exactly what I already do, to ignore this test in Excel XP since I can't detect if 'Trusted Access to Visual Basic Project' is enabled. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    I do have a VBA digital signature which I installed but still the user has to check the 'Trusted Access to Visual Basic Project' item for my code to correctly detect missing references. It's highly ulikely that this value is actually enabled unless the end user is a developer too.

    I can't find any references in the Help to 'trust' or 'sources' or 'vbaproject'. A bunch of things come up on search for 'security' which is helpful but just to say what I want to do can't be done.

    Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing Broken References (VBA/Word/2000)

    You should be able to use a reference to the VBA extensibility library or the VB extensibility library and that will enable you to deal directly with the IDE like this:

    <pre>Public Function EnumRefs()
    Dim ref As VBIDE.Reference
    Dim element As Variant

    For Each ref In VBE.ActiveVBProject.References
    Debug.Print ref.name, ref.Description, ref.FullPath, ref.BuiltIn
    Next ref
    Set ref = Nothing
    End Function</pre>


    That should work even if you have a broken reference.
    Charlotte

  14. #14
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    This is really getting weird. I wasn't able to run/compile your code in Excel 2000 or Excel 2002. I did get this to work:

    <pre>Public Function EnumRefs()
    Dim ref As Object
    Dim element As Variant

    For Each ref In ThisWorkbook.VBProject.References
    Debug.Print ref.Name, ref.Description, ref.FullPath, ref.BuiltIn
    Next ref
    Set ref = Nothing

    End Function</pre>


    However.... depending on the reference I chose to load, some of the fields didn't work (.Description or .FullPath for instance). Also, when I deliberately removed a file that was referenced I didn't see any MISSING text in the references list the next time that workbook was opened. Shouldn't I see MISSING at least? Has Excel 2002 done away with this? I did get a warning at workbook open that a previously referenced template (.xla file) was not available but no error about a missing Acrobat.tlb file that I referenced (for Acrobat4). I couldn't find anything in the help files about this either. <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>

    At this point, I'll give it up and just not run this check if Excel 2002. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Enough time has been wasted. I hope the original poster has found a suitable solution.

    Thnx, Deb

  15. #15
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Broken References (VBA/Word/2000)

    That worked!! I've included my code below:

    <pre>Public Function EnumRefs()
    Dim ref As VBIDE.Reference
    Dim element As Variant

    On Error GoTo ErrorHandler1
    For Each ref In VBE.ActiveVBProject.References
    Debug.Print ref.Name, ref.Description, ref.FullPath, ref.BuiltIn
    GoTo AfterErrorHandler1

    ErrorHandler1:
    If Err.Number = 35021 Then 'Which occurs for a missing reference.
    VBE.ActiveVBProject.References.Remove ref
    Else
    MsgBox Err.Number & " - " & Err.Description & vbCrLf & vbCrLf & _
    "Send an e-mail to Documentation.Training@sabre.com" & vbCrLf & vbCrLf & _
    "with this error number and description."
    End If
    AfterErrorHandler1:

    Next ref
    Set ref = Nothing
    End Function</pre>


    However, I'm with jujuraf with regards to the strange items that seem to be unavailable with a missing template. For example, the following are highlighted when I debug prior to removing the missing reference to the previously attached template:

    Dim vbProj As VBProject (actually the word "Dim" is not highlighted, but I wanted to give you the entire line)
    Dim chkRef As Reference (actually the word "Dim" is not highlighted, but I wanted to give you the entire line)
    Chr$ (as a part of the line FileTypeFilter = "TechTemplate.dot File" + Chr$(0) + "TechTemplate.dot")
    Space$ (as a part of the line FName.lpstrFile = Space$(254))
    Trim (as a part of the line FileNameAndPath = Trim(FName.lpstrFile))

    etc.

    Obviously these are not a part of the template file, but some other library, which is for some reason unavailabe when there are missing references. Any ideas?

    Thanks again for the help!!
    Troy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •