WorkBookName Custom Function (Excel 2000)

Topic

New Reply

Hello y’all,

I’ve successfully created a custom (user defined) function that returns the active Workbook’s name.

Function WORKBOOKNAME() As String
‘Returns the workbook name of the cell
‘that contains the function
WORKBOOKNAME = Application.Caller.Parent.Parent.Name
End Function

But now I have a client who wants the path as well as the Workbook Name.

Any suggestions how to return the Workbook’s oath and file name with a similar user define function?

Thanks so much,
Rich

Reply | Quote

Replies

The formula

=CELL(“filename”)

or

=CELL(“filename”,A1)

will return the full path + file name of the active workbook.

I don’t know about workbook oaths…

Reply | Quote

Hans,

Yes, thanks for the =CELL tip.

And, I was trying to find if there is a way that I could get the path (not oaths ) in my user defined function? Any ideas?

Thanks,
Rich

Reply | Quote

WORKBOOKNAME = Application.Caller.Parent.Parent.path

Steve

Reply | Quote

Hi Steve,

Wow, we’re really close, most probably because I wasn’t as clear as I should have been. Your suggestion certainly returned H:Excel ResourcesRich Created Add-Ins

I was looking for something that included the Workbook name as well. It would return, for example: H:Excel ResourcesRich Created Add-Insmyworkbook.xls

Thanks,
rich

Reply | Quote

Use FullName instead of Path. Or the following formula:

=SUBSTITUTE(LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””)

Reply | Quote

Hans,

Your FullName suggestion worked perfectly! It is just what we needed. You’re a genius, and thanks so much!

So, it looked like this:

Function WORKBOOKPATH() As String
‘Returns the workbook name of the cell
‘that contains the function
WORKBOOKPATH = Application.Caller.Parent.Parent.FullName
End Function

I distributed both of these, WORKBOOKNAME and WORKBOOKPATH, via an Excel Add-in.

Tested successfully in MS Excel 2000 in WIN XP Pro, MS Excel 2004 in MAC OS 10.3.9, and MS Excel v.10 in MAC OS 10.3.9.

Thanks so very much, all of you,
Rich

Reply | Quote