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
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
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.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
(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
Dim TempFileName As String
Dim ExeName As String
Dim Res As Long
' GetTempFile comes from
TempFileName = GetTempFile(InFolder:=vbNullString, _
FileNamePrefix:=vbNullString, Extension:="xls", CreateFile:=True)
ExeName = String$(260, vbNullChar)
Res = FindExecutable(TempFileName, vbNullString, ExeName)
If Res > 32 Then
ExeName = TrimToNull(ExeName)
Excel.Application.ActiveWorkbook.VBProject.Referen ces.AddFromFile _
Debug.Print "Find Executable failed"
Function TrimToNull(Text As String) As String
' 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)
TrimToNull = Text
"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
> reference or remove a refence to a Type library. (I have such code, but
> code is not the issue.) Say I'm using automation and controlling Excel
> Access vba and I want to set or unset a reference to the Excel Type
> by code. Different users may have their Excel Type Library installed in
> different places. How do you locate that Type Library file using vba
> 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
> in Windows but not sure if an operator like that is useful for my
> Thanks in advance for any help.