I want excel to retrive the name of the worksheet in a cell?

[clausboss]

Hi

I know that I can make =CELL("filename") to get the file name but if I only wante to retrive the sheet name in file is this possible.



Claus

 

[justme]

You were right the first time. I did not want the path. I'm just trying to fill in different information in separate cells. I see that XL doesn't want to give me the folder name using your previous workbook name post. Would you alter Aladin's formula?

Thanks in advance,

 

[Greg Truby]

See my previous post. One formula returns path & wb name, other returns only the path.

 

[justme]

You were right the first time. I did not want the path. I'm just trying to fill in different information in separate cells. I see that XL doesn't want to give me the folder name using your previous workbook name post. Would you alter Aladin's formula?

Thanks in advance,

 

[Greg Truby]

Having trouble getting the "submit" button to work properly?

 

[justme]

It's so hard to concentrate with egg on your face!

=LEFT(CELL("filename",A1),SEARCH("\",CELL("filename",A1)))

I am playing with your formula and changed the ] to \. The return was C:\ When I changed the formula to

=LEFT(CELL("filename",A1),SEARCH("\",CELL("filename",A1))+3 [hoping to to out three \ to pull only the folder name. The formula returns c:\Doc


Is there a way to pull only the folder name?

I'll try to push the button only once this time.......

 

[Greg Truby]

I'm assuming that when you say "folder name" you mean just the folder at the end of the path. This is tough with just a formula since you don't know how many backslashes will be in the path and you're working right-to-left. But very easy with VBA. Just drop the following UDF into a standard module and then type =udfMyFolder() into a cell.

<font face=Courier New>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> udfMyFolder() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> varPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    varPath = Split(ThisWorkbook.Path, Application.PathSeparator)
    udfMyFolder = varPath(UBound(varPath))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

 

[justme]

Perfect, thanks.

 

[Greg Truby]

JustMe,

I just happened to be stepping through some code of my own and ran through the following function. It's not a UDF to be used in a cell, but rather to be called from a VBA subroutine. But it just fit so nicely with this theme that I thought I'd post it in case you'd ever need something like it.

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Function HighestPathBranch(ByVal strPath As String) As String
'______________________________________________________________________

' Returns the highest level folder of a path string

    Dim strPS As String, varParts As Variant
    
    strPS = Application.PathSeparator
    If Right(strPath, 1) = strPS Then strPath = Left(strPath, Len(strPath) - 1)
    varParts = Split(strPath, strPS)
    HighestPathBranch = varParts(UBound(varParts))

End Function