Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Current Workbook Path | Excel Programming | |||
Current Workbook Path | Excel Programming | |||
Current path to Qualified Path | Excel Programming | |||
path to current file... | Excel Programming |