Setting a reference to a libray in code

[Syvers]

Hi

I am trying to reset references to Excel and Word from Access 2007 i.e. code
developed on 2007 with office 2007 but when dstributed to users the reference
is broken because they are on say office 2000.

I have read the link provided in previous posts on this subject but it
doesnt seem to be of any help to me.

What i do is look through the references to see if any are broken (i already
know the excel one is though by looking at tools > references) so

For each ref in References
if ref.isbroken then
'fix the reference
end if
next

I was then going to fix the broken ones but when i get to a broken ref it
cannot access the name property and i cannot remove the ref either, but if i
just try to add the reference using AddFromPath i get an error telling me the
reference is allready in use. Well this is no use because it is missing and i
cant remove it because i cannot extract its name to work out which one is
broken!!

The messages I get when trying to set a reference object to excel (Set
missingRef = References!Excel) is error 2147319779 (8002801d) Method item of
object references failed.

That leaves me thinking ok the reference is not available so how can i
create one so i try to addfromfile (References.AddFromFile("c:\Program
Files\MicrosoftOffice\Office\Excel9.olb") and i get RunTime error 32813 -
Name conflicts with existing module, project or library.


I have managed to remove it manually by unticking the missing excel 12
reference then adding the excel9 reference in code but that is no use either,
i need to be able to remove the broken reference in code so i can replace it
(according to the help files!!!).

Can anyone help?

Thanks

Paul

 

[Douglas J. Steele]

When a reference is broken, you can't access most of its properties.
However, there's no reason why you can't delete it. Inside your If
statement, use the Remove method of the References collection:

For each ref in References
if ref.isbroken then
References.Remove ref
end if
next ref

That having been said, I'd recommend strongly against trying to reset the
references such as you are. Use Late Binding instead, so that you don't need
to set a reference, and it'll work with any version of Word or Excel unless,
of course, you're using features that only exist in a particular version.
Even in that case, though, resetting the reference won't work if the
referenced version doesn't have the feature.

 

[Syvers]

Hi Douglas

I should have mentioned i tried that method References.Remove Ref but it
does not recognise the ref as existing, i.e. i cant even get ref.name, seems
like a complete catch 22. Cant add because it exists, cant remove because it
cant be found!!

Anyway, have been chatting to a colleague about late binding and although we
are nearly at the end of our application development we think it best to
review all references to word and excel and use the CreateObject(".....")
method aka late binding.

Thanks for your help and advice

Paul

 

[Marshall Barton]

I should have mentioned i tried that method References.Remove Ref but it
does not recognise the ref as existing, i.e. i cant even get ref.name, seems
like a complete catch 22. Cant add because it exists, cant remove because it
cant be found!!

Anyway, have been chatting to a colleague about late binding and although we
are nearly at the end of our application development we think it best to
review all references to word and excel and use the CreateObject(".....")
method aka late binding.

You might find this article enlightening and further
encouragement to pursue late binding:
http://www.trigeminal.com/usenet/usenet026.asp?1033

 

[Douglas J. Steele]

Unlike, say, the Delete method to delete a table from the TableDefs
collection, you don't need the name of the reference to use the Remove
method. The Remove method expects an actual reference to the object, not its
name. Did you try the code I suggested?

 

[Paul Shapiro]

I used to use code similar to what you had in the beginning of this thread
to replace broken references with downlevel references. But I don't think
it's working now. There are a few properties you can read for a broken
reference. Here's the code that used to work. If the ref.isBroken line is
throwing an error, you could catch that error and thereby know that the
reference is broken. The trick then is to know the correct guid's and
version numbers to try for adding the downgraded version references.

Alternatively, you can distribute the application in the oldest version in
use. Office references are generally upgraded automatically to the newer
version. I use Virtual PC to keep one or more virtual machines with the os
and office versions that I need to distribute. You can manually adjust the
references in the VM, and then distribute a "clean" version for users with
older software.

For Each ref In Access.References
strGUID = ref.Guid
'Unfortunately, this seems to fail on the next line when a reference
is broken.
If ref.IsBroken Then
'Print the available details for broken reference
Debug.Print "Reference: " & strGUID _
& " (Broken) v." & ref.Major & "." & ref.Minor
Select Case strGUID
Case "{00062FFF-0000-0000-C000-000000000046}"
'Outlook 9.3 (Office 2007) - try using Outlook 9.2 (Office
2003)
References.Remove ref
References.AddFromGuid strGUID, 9, 2
Case "{00020905-0000-0000-C000-000000000046}"
'Word 8.4 (Office 2007) - try using Word 8.3 (Office 2003)
References.Remove ref
References.AddFromGuid strGUID, 8, 3
Case "{00020813-0000-0000-C000-000000000046}"
'Excel 1.6 (Office 2007) - try using Excel 1.5 (Office 2003)
References.Remove ref
References.AddFromGuid strGUID, 1, 5
'ElseIf Len(Dir(ref.FullPath)) > 0 Then 'Cannot read FullPath
when ref is broken
' References.AddFromGuid strGUID, ref.Major, ref.Minor
Case Else
strMsg = "Reference " & strGUID & " cannot be found." & _
vbCrLf & vbCrLf & "Please contact the programmer."
Beep
MsgBox strMsg, vbCritical, "Cannot run the program"
End Select
End If
Next ref

 

[Syvers]

Hi Douglas, yeah i had already tried that code but got an error message along
the lines of the ref object not existing. Have updated code now anyway to use
late binding, i think it is probably for the best and there wasnt as much use
as i thought so quite simple to update.

 

[Syvers]

Unifortunately althought Ref.IsBroken is showing as True i cant use
References.Remove Ref it shows an error.

I should point out though that this does work in my ADP on 2007 i.e. i can
remove them if i choose and then add them back which i did for test purposes.
As soon as the reference is missing though it fails you cant remove it
because it is missing and cant add it because it conflicts with one that
already exists, yet its broken!!!

Anyway, we have gone for late binding solution now as it seems the best
option, will put an arror message out if excel or word doesnt exist on the
users machine.

Thanks very much for your help.

Paul

 

[Syvers]

P.S. The link on your MVP page which is headed up Easily Change Application
Configurations Using INI Files is pointing to the same page as Effectively
Use Many-to-Many Relationships in Microsoft Access. I mention it because i
was interested in reading the INI files document.

Thanks again for your help.

 

[Douglas J. Steele]

Hmm. Looking at
http://my.advisor.com/Articles.nsf/vWriterID?OpenView&RestrictToCategory=STEED
they don't seem to list the article (and others seem to be missing as well).

I'm on the road right now: will look into it when I get home.