VBA References

[Guest]

I ran into a unique situation this morning where my boss couldn't access the
functions of a database I created in order to automate some tasks that
normally take 2 hours to do manually. Our "opening" person is out this
morning on training and I'm the closer so he was pretty hacked to find out
that he was going to have to do the process manually this morning (he could
have just called me... but oh well).

Long story short, I have Office 2000 installed on my machine at work, so I
have the Excel 9.0 Object Library. He has Office 2003 just recently
installed (and it will be weeks before I have it on mine) so he had the Excel
11.0 Object Library. As you can guess, it tried to treat my 9.0 as "missing"
probably b/c the file is loaded on a different network path (our company
doesn't load programs onto the local machine.... sigh....).

I know if I look at the VBE.ActiveProject.References I can see the
references that I DO have linked. Is it possible to see the ones available
that I DON'T have linked though like you can see when you go to Tools ->
References? I know that Visual Studio lets you see these too when you try to
add COM refs.

If I could do that, I could pretty easily just do some instr's and find what
version of Excel they have and then link accordingly. Would also be nice to
do it to see if they have Microsoft Speech Object Library installed and a few
others.

 

[Dirk Goldgar]

I ran into a unique situation this morning where my boss couldn't
access the functions of a database I created in order to automate
some tasks that normally take 2 hours to do manually. Our "opening"
person is out this morning on training and I'm the closer so he was
pretty hacked to find out that he was going to have to do the process
manually this morning (he could have just called me... but oh well).

Long story short, I have Office 2000 installed on my machine at work,
so I have the Excel 9.0 Object Library. He has Office 2003 just
recently installed (and it will be weeks before I have it on mine) so
he had the Excel
11.0 Object Library. As you can guess, it tried to treat my 9.0 as
"missing" probably b/c the file is loaded on a different network path
(our company doesn't load programs onto the local machine....
sigh....).

I know if I look at the VBE.ActiveProject.References I can see the
references that I DO have linked. Is it possible to see the ones
available that I DON'T have linked though like you can see when you
go to Tools -> References? I know that Visual Studio lets you see
these too when you try to add COM refs.

If I could do that, I could pretty easily just do some instr's and
find what version of Excel they have and then link accordingly.
Would also be nice to do it to see if they have Microsoft Speech
Object Library installed and a few others.

This is a thorny problem. The Reference object (see the online help
topic) has an IsBroken property, but a very knowledgeable person has
advised against using it. See MichKa's article here:

http://www.trigeminal.com/usenet/usenet026.asp?1033
INFO: How to guarantee that references will work in your
applications

I believe your best bet is not to use *any* references besides those
that are basic to Access, which Access can usually fix up on its own.
Instead, use late binding to work with Excel or other automation
objects. Then you don't have to worry about the references.

Many people who design for environments like yours use early binding
while developing the code, because of the convenience of the
intellisense prompts, but then change the code to the late-binding
version before distributing the application. This can be done using
conditional compilation via the "#If...Then...#Else" compiler directive.

 

[Douglas J. Steele]

Consider using Late Binding instead. Tony Toews has an introduction to the
topic at http://www.granite.ab.ca/access/latebinding.htm

Once you've got Late Binding, you'd simply trap for the 429 error after the
CreateObject call to see whether they have the particular application
installed.

 

[Guest]

Thanks for the info guys that got me up and running nicely.

However, I'd still like to know for my own reference how to programmatically
see what COM references the user has available and add/remove them
programmatically.

Take this example for instance, my boss has Microsoft Speech SDK 5.1
instaleld, but the person opening doesn't. I can look at the References
object to see if they have it loaded and use Late Binding to create the
object. However, of course as you know if you have even one Missing ref then
nothing will work at all, and if I programmatically remove it for her, then I
have to readd it for me or for my boss.

So what I would like to know is how to programmatically pull a listing of
what COM refs are available for a particular system which I'm more than
likely sure has to be done through the Win32 API. That way I can see if a
user has the ref available and programmatically add it (then remove it at the
end of execution).

 

[Rick Brandt]

Thanks for the info guys that got me up and running nicely.

However, I'd still like to know for my own reference how to
programmatically see what COM references the user has available and
add/remove them programmatically.

Take this example for instance, my boss has Microsoft Speech SDK 5.1
instaleld, but the person opening doesn't. I can look at the
References object to see if they have it loaded and use Late Binding
to create the object. However, of course as you know if you have
even one Missing ref then nothing will work at all, and if I
programmatically remove it for her, then I have to readd it for me or
for my boss.

So what I would like to know is how to programmatically pull a
listing of what COM refs are available for a particular system which
I'm more than likely sure has to be done through the Win32 API. That
way I can see if a user has the ref available and programmatically
add it (then remove it at the end of execution).

I don't understand. If you use late binding then you don't need the
reference.

 

[Guest]

Ah so you don't need references *AT ALL* if you are doing Late Binding then?

i.e. If I want to CreateObject a Microsoft.Speech object, the user doesn't
need to still have the Microsoft Speech Object Library reference checked? I
also want to be able to trap errors too though if I try to create the object
and it doesn't exist.

 

[Douglas J. Steele]

Using Late Binding is infinitely preferable. You won't run into versioning
problems.

With Late Binding, you don't need the reference at all. In the case of the
Speech SDK, your code is probably something like:

Dim objSpeech As Speech.Object

Set objSpeech = New Speech.Object

(forgive me: I don't work with the SDK, so I don't know what you actually
have instead of Speech.Object)

WIth Late Binding, you'd have no reference, and you'd change your code to:

Dim booNoSDK As Boolean
Dim objSpeech As Object

On Error Resume Next
booNoSDK = False
Set objSpeech = CreateObject("Speech.Object"
If Err.Number = 429 Then
booNoSDK = True
MsgBox "Sorry, you don't have the Speech SDK installed"
End If
On Error GoTo ErrorHandler

Now, in the rest of your code, you check the value of booNoSDK, and bypass
the stuff dealing with speech if its value is True.

 

[Guest]

Nah my code is like this:

Option Compare Database
Option Explicit

' This is some utility code that really doesn't serve a functional purpose,
it just gives the capability
' to use the TTS (Text to Speech) capabilities from Microsoft if it's
installed.

Public Function SpeakText(ByVal textToSpeak)

' I normally don't use CreateObject as it's bad for anyone trying to
read my code...
' but here I do because I don't want execution to fail if the user
doesn't have the Speech API (SAPI) installed.
' If it is though, then I create a voice object, set it to the first
voice, and have it speak the text I want.

If DetermineSpeechCapability = True Then

Dim spV, iSpeechTokens
Set spV = CreateObject("Sapi.spVoice")
Set iSpeechTokens = spV.GetVoices()
Set spV.Voice = iSpeechTokens.Item(0)
Call spV.Speak(textToSpeak)
Set iSpeechTokens = Nothing
Set spV = Nothing

End If

End Function

Public Function DetermineSpeechCapability() As Boolean

' Here I just loop through the references and see if the user has a ref
to SAPI

DetermineSpeechCapability = False
Dim i, icnt As Long
icnt = Application.VBE.ActiveVBProject.References.Count
For i = 1 To icnt
If Application.VBE.ActiveVBProject.References.Item(i).Description =
"Microsoft Speech Object Library" And
Application.VBE.ActiveVBProject.References.Item(i).IsBroken = False Then
DetermineSpeechCapability = True
End If
Next
End Function

Obviously checking the references part is useless there since I had to
remove the reference. However, I'd really like to know if the user HAS
Sapi.spVoice first somehow before trying to do a CreateObject, as that would
be preferrable to trying to trap an ErrNumber. Surely there's gotta be a way
to enumerate what COM libs a particular machine has installed.

 

[Douglas J. Steele]

There is, but WHY would that be preferable to trapping the error?

The COM objects are all stored in the registry (in HKCR, and in HKLM, I
believe). To list them all, you'd have to traverse through those parts of
the registry. To determine whether a specific one exists, you'd need to know
the GUID associated with the object and look in that part of HKCR\CLSID

 

[Guest]

Heh, found a link on CodeGuru saying to HKCR\TypeLib just before I saw this
post... yeah, I'll live with catching an ErrNumber

Thanks for all the help!