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.

Thanks, Chip. I'll give it a try. Seems quite ingenious.

"Chip Pearson" wrote:

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.

If you know the GUID of the type lib, you can also add a reference using the
GUID:

Sub AAA()
Dim GUID As String
Dim Major As Long
Dim Minor As Long

Const GUID_Word = "{00020905-0000-0000-C000-000000000046}"
Const GUID_Excel = "{00020813-0000-0000-C000-000000000046}"

Const MajorDefaultToLatest = 0
Const Major97AndBeyond = 1

Const MinorDefaultToLatest = 0
Const Minor2007 = 6
Const Minor2003 = 5
Const Minor2002 = 4
Const Minor2000 = 3
Const Minor97 = 2

GUID = GUID_Excel
Major = Major97AndBeyond
Minor = Minor2007

With ThisDocument.VBProject.References
On Error Resume Next
.Remove .Item("EXCEL")
On Error GoTo 0
.AddFromGuid GUID, Major, Minor
End With
End Sub


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




"Perico" wrote in message
...
Thanks, Chip. I'll give it a try. Seems quite ingenious.

"Chip Pearson" wrote:

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.