A user's special folders (e.g., "My Documents" or "Desktop") are stored in
the user's "Profile Directory". Using the code on
http://www.cpearson.com/excel/SpecialFolders.htm you get the user's Profile
Directory or a specific folder like "My Documents".

If you're asking about the Excel typelib in particular, you're in luck,
because Excel's typelib is in the exe itself. Thus, you can use the
FindExecutable API function to get the full file name of Excel by passing
the name of an existing XLS file to FindExecutable.

The following code will create a temporary xls file in the user's designated
Temp folder (see http://www.cpearson.com/excel/Workin...AndFolders.htm
for code to work with temporary files and folders), call FindExecutable to
get the path the Excel.exe, kill the temp file, and then use
VBProject.References.AddFromFile to add the Excel typelib to VBProject.

For other typelibs, you may need to do a few lookups in the
HKEY_CLASSES_ROOT registry to get the actual location of the typelib.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




Public Declare Function FindExecutable Lib "shell32.dll" Alias
"FindExecutableA" ( _
ByVal lpFile As String, _
ByVal lpDirectory As String, _
ByVal lpResult As String) As Long


Sub AAA()

Dim TempFileName As String
Dim ExeName As String
Dim Res As Long

' GetTempFile comes from
' http://www.cpearson.com/excel/Workin...AndFolders.htm
TempFileName = GetTempFile(InFolder:=vbNullString, _
FileNamePrefix:=vbNullString, Extension:="xls", CreateFile:=True)

ExeName = String$(260, vbNullChar)
Res = FindExecutable(TempFileName, vbNullString, ExeName)
Kill TempFileName
If Res 32 Then
ExeName = TrimToNull(ExeName)
Excel.Application.ActiveWorkbook.VBProject.Referen ces.AddFromFile _
FileName:=ExeName
Else
Debug.Print "Find Executable failed"
End If


End Sub

Function TrimToNull(Text As String) As String
''''''''''''''''''''''''''''''''''''''''''''''
' TrimToNull
' Returns the portion of Text to the left
' of the first vbNullChar.
''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos As Integer
Pos = InStr(1, Text, vbNullChar, vbTextCompare)
If Pos Then
TrimToNull = Left(Text, Pos - 1)
Else
TrimToNull = Text
End If
End Function




"Perico" wrote in message
...
How do you find a file that may be in different locations / folders on
different users pc's? Example, let's say I want to use a function to set
a
reference or remove a refence to a Type library. (I have such code, but
that
code is not the issue.) Say I'm using automation and controlling Excel
from
Access vba and I want to set or unset a reference to the Excel Type
Library
by code. Different users may have their Excel Type Library installed in
different places. How do you locate that Type Library file using vba
code?

I've seen "%", which I think is a placeholder or wildcard of some sort
(please clarify - I've been curious exactly what it is), used with MSDos
and
in Windows but not sure if an operator like that is useful for my
objective.

Thanks in advance for any help.