Just noticed something that screws up a lot of my plans for checking
and adding references via code:  When a reference is broken/"MISSING"
I can't access any of its other useful properties to identify it.

I'll be coding this add-in in 2000 and exporting to 97, 2000 and 2002.
When a '97 machine receives the file, the Excel ref points to version
9.0 and therefore shows up as 'missing.'  The logical thing to do is
remove it and add the right one via code.  But I seem able only to
remove *any* broken reference -- by looping through the References
collection and testing the IsBroken property.  That is, I can't ask
for a broken ref's Name, Description, or FullPath properties (actually
they're methods, how odd) to be sure it's the one I should be removing.
Doing so gives the "Method XXX of object Reference failed" error.

Granted, since broken references aren't useful, I could have my code
just remove *any* broken refs it found, then add the Excel ref after
an On Error Resume Next.  But that seems like bad programming practice.
Is it?  Any ideas?

--

"Life is nothing if you're not obsessed." --John Waters

Bad programming practice or not - I've been forced to a similar solution!

Regards BosseH

Maybe I don't understand you correctly and got confused by your different questions regarding this topic, but why wait for a reference to break? Can't you create them in either the lowest version (in your case '97) or remove the references when you're distributing and set them all with code?

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/

Mm, the different questions must look pretty odd.  I was trying to approach
it from two different angles.

Yes, I'd like to be able to edit the add-in in '97, but after October, that
won't be possible, as the '97 machine will be upgraded.  Also, it's not my
main machine now (I have access to it only in the evening), so I'd have
problems issuing a fix for a '97 user during the day.  So I'm looking for
a way to do all editing in 2000.  As you proved, if I do that, 2002 users
will be fine; but in '97 the add-in opens with that reference broken.

And while setting it via code usually does work, for some reason the code
that sets the reference causes Word to lose the focus; so although Word
launches OK, the user can't just start typing without clicking in the
window first, which is annoying.

The code I'm now using (below), which first removes ANY broken reference,
does work, but as I say, it doesn't seem right to do that.  And it also
launches Word without the focus, as above.  It seems anything that cycles
through the References collection does that.  Is there a way around that?

Thanks again for any further help.
Mark

Current code (for editing in 2000 and distributing to all versions):

Public Function NeedsExcelRef() As Boolean
Dim i As Long, ExcelFound As Boolean
' Euthanize any broken refs first
For i = ThisDocument.VBProject.References.Count To 1 Step -1
     If ThisDocument.VBProject.References(i).IsBroken = True Then
         ThisDocument.VBProject.References.Remove _
            ThisDocument.VBProject.References(i)
     End If
Next i
DoEvents
For i = ThisDocument.VBProject.References.Count To 1 Step -1
     If ThisDocument.VBProject.References(i).Name = "Excel" Then
          ExcelFound = True
          Exit For
     End If
Next i
If ExcelFound = False Then NeedsExcelRef = True
End Function

'----------------------

Sub AutoExec()
If NeedsExcelRef = True Then Call SetExcelRef
Call RestOfAutoExec
End Sub

'----------------------

Sub RestOfAutoExec
 <---Must put all of the AutoExec's code here, otherwise compiler flags
     very ordinary functions in it (Chr, Environ, etc.) as 'unknown.'
End Sub

'----------------------

Sub SetExcelRef()
On Error Resume Next
With ThisDocument.VBProject.References
  Select Case _
     Mid$(Application.Version, 1, InStr(Application.Version, ".") - 1)
   Case "8"
     .AddFromFile "C:\Program Files\Microsoft Office\Office\Excel8.OLB"
   Case "9"
     .AddFromFile "C:\Program Files\Microsoft Office\Office\Excel9.OLB"
   Case "10"
     .AddFromFile "C:\Program Files\Microsoft Office\Office10\Excel.EXE"
  End Select
End With
DoEvents
On Error GoTo 0
End Sub