Return file from a path string

[Steph]

I have a string variable that is a path to a file:
C:\folder1\folder2\folder3\folder4\folder5\file.xls

How can I create a new variable that is simply the file name.xls? Thanks!

 

[Tom Ogilvy]

If you have xl2000 or later:

sStr = "C:\folder1\folder2\folder3\folder4\folder5\file.xls"
? Right(sStr,len(sStr)-instrrev(sStr,"\"))
file.xls

demo's from the immediate window.

 

[Bob Phillips]

One way Steph

Dim sFile
Dim ary

sFile = "C:\folder1\folder2\folder3\folder4\folder5\file.xls"

ary = Split(sFile, "\")
MsgBox ary(UBound(ary))

or more directly, but less efficiently

Dim sFile

sFile = "C:\folder1\folder2\folder3\folder4\folder5\file.xls"
MsgBox Split(sFile, "\")(UBound(Split(sFile, "\")))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steph]

Thank you so much

If you have xl2000 or later:

sStr = "C:\folder1\folder2\folder3\folder4\folder5\file.xls"
? Right(sStr,len(sStr)-instrrev(sStr,"\"))
file.xls

demo's from the immediate window.