Re: strip away the directory structure on file name

Your "spread*** formula" roots are showing... inside VBA, the third argument to the Mid function is optional. If you leave it out, the remainder of the text is returned. So, you could have written you active line this way...

ShowFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)

As long as we are talking one-liner solutions, here are a few other ways to do this (but probably not as efficiently)...

ShowFileName = Split(strFullPath, "\")(UBound(Split(strFullPath, "\")))

ShowFileName = StrReverse(Split(StrReverse(strFullPath), "\")(0))

ShowFileName = Replace(strFullPath, Left(strFullPath, InStrRev(strFullPath, "\")), "")

Rick

"Ron Coderre" <RonCoderre@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1AE346FA-57BA-42FC-84FF-F8806B9283E0@xxxxxxxxxxxxxxxx
Unfortunate choice of function name....GetFileName is a method of the
FileSystemObject. It's generally a bad idea to reuse an existing item.

Consequently, this would be better:
Function ShowFileName(strFullPath As String)
ShowFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1, 255)
End Function

***********
Regards,
Ron

XL2003, WinXP


"Ron Coderre" wrote:

Try something like this:

Function GetFileName(strFullPath as String)
GetFileName = Mid(strFullPath ,InStrRev(strFullPath ,"\")+1,255)
End Function

To test:
In a *** cell
=GetFileName("C:\My Documents\directory\file.hex")
returns: file.hex

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"a924fan@xxxxxxxxx" wrote:

> I can now import a file and store the file name into a cell. Thanks
> Dave. I get the directory structure too. "C:\My Documents\directory
> \file.hex". is there a way in a macro to strip away the directory
> structure and leave only "file.hex"
>
>

.

Loading