current toolbar file path?
 

i would like to display in a msgbox the current toolbar file path (similar to
how the following displays the current workbook's path: MsgBox
ThisWorkbook.FullName). is this possible?

[note: if the answer is to perform a file search & then use the results in a
msgbox, i have trouble getting file search to consistently work. today it
won't display any results for a *.xlb search, yet previously it led me to
where my file was saved. i never know if "no results" really means no
results. i'm sure it's me, but i haven't figured it out.]

thank you.
elizabeth

current toolbar file path?
 

I don't know any built-in method to return the xlb path in excel macro.
if using Excel 2000 and later, how about something like this:

Function XLBPath() 'for Excel 2000 and later
Dim XLBName As String, XLStart As String
Dim nVer As Integer, i As Integer
nVer = Int(Val(Application.Version))
Select Case nVer
Case Is < 9: Exit Function
Case 9: XLBName = "Excel.xlb"
Case Else: XLBName = "Excel" & nVer & ".xlb"
End Select
XLStart = Application.StartupPath
i = InStrRev(XLStart, "\")
If i 0 Then XLBPath = Left(XLStart, i) & XLBName
End Function

--
HTH,

okaizawa


Elizabeth wrote:
i would like to display in a msgbox the current toolbar file path (similar to
how the following displays the current workbook's path: MsgBox
ThisWorkbook.FullName). is this possible?

[note: if the answer is to perform a file search & then use the results in a
msgbox, i have trouble getting file search to consistently work. today it
won't display any results for a *.xlb search, yet previously it led me to
where my file was saved. i never know if "no results" really means no
results. i'm sure it's me, but i haven't figured it out.]

thank you.
elizabeth

current toolbar file path?
 

Okaizawa:
THANK YOU!
I added "MsgBox XLBPath" at the end of your function & it displayed exactly
where my Excel11.xlb file is located. I was not able to get .FileSearch or
Dir() to do this. Yea! As long as my Excel 2003 users do not change their
toolbar file name from the default name, this will do the trick (& I'm not
sure if Excel would allow them to change the default name). Because I am new
to VBA, I found functions within your code that are new to me & will be
useful for other purposes (e.g., Val & InStrRev). What a bonus! Thank you
so much for taking the time to help me.
Elizabeth


"okaizawa" wrote:

I don't know any built-in method to return the xlb path in excel macro.
if using Excel 2000 and later, how about something like this:

Function XLBPath() 'for Excel 2000 and later
Dim XLBName As String, XLStart As String
Dim nVer As Integer, i As Integer
nVer = Int(Val(Application.Version))
Select Case nVer
Case Is < 9: Exit Function
Case 9: XLBName = "Excel.xlb"
Case Else: XLBName = "Excel" & nVer & ".xlb"
End Select
XLStart = Application.StartupPath
i = InStrRev(XLStart, "\")
If i 0 Then XLBPath = Left(XLStart, i) & XLBName
End Function

--
HTH,

okaizawa


Elizabeth wrote:
i would like to display in a msgbox the current toolbar file path (similar to
how the following displays the current workbook's path: MsgBox
ThisWorkbook.FullName). is this possible?

[note: if the answer is to perform a file search & then use the results in a
msgbox, i have trouble getting file search to consistently work. today it
won't display any results for a *.xlb search, yet previously it led me to
where my file was saved. i never know if "no results" really means no
results. i'm sure it's me, but i haven't figured it out.]

thank you.
elizabeth