Between Function?

[robfo0]

Hi everyone,

Ive looked on the board and found similar problems, but nothing that quite matches mine.

Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

s:FileServerFilesYear2002

 

[dk]

Hi,

Not sure about the worksheet function (would have imagined some sort of array formula) but you can use VBA like this:-

Sub Test()
Dim strFullPath As String, strFileName As String
Dim lngCharLoop As Long

strFullPath = "s:FileServerFilesYear20023-02Jones, Rob.xls"

For lngCharLoop = Len(strFullPath) To 1 Step -1
    If Mid$(strFullPath, lngCharLoop, 1) = "" Then Exit For
Next lngCharLoop

strFileName = Right(strFullPath, Len(strFullPath) - lngCharLoop)
MsgBox strFileName
End Sub

Hope it helps,
D

 

[Jack in the UK]

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

 

[Aladin Akyurek]

On 2002-03-23 12:12, robfo0 wrote:
Hi everyone,

Ive looked on the board and found similar problems, but nothing that quite matches mine.

Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

s:FileServerFilesYear2002

 

[robfo0]

thanks guys!

 

[Jay Petrulis]

On 2002-03-23 12:12, robfo0 wrote:
Hi everyone,

Ive looked on the board and found similar problems, but nothing that quite matches mine.

Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

s:FileServerFilesYear2002

 

[Tommy Bak]

Hi
This code can also do the job:

Function NameFromPath(strPath As String) As String
Dim lngPos As Long
Dim strPart As String
lngPos = InStrRev(strPath, "") + 1
If lngPos > 0 Then strPart = Mid(strPath, lngPos, Len(strPath) - lngPos - 3)
NameFromPath = strPart
End Function

regards Tommy