Get file name?

[nikko50]

Hi all,

How to I get the file name of the variable FilePath. Basically just want to get filename in this case it would be "my doc 5.xls"

FilePath = "X:\users\slef\2015\1 June\my doc 5.xls"

 

[Claus8528]

There's an easy guide at this page Extract Filenames from Filepath in Excel

 

[VBA Geek]

msgbox DIR(FilePath,vbNormal)

 

[dmt32]

Hi,

try this:

Sub ShowFileName()
Dim FilePath As String, FileName As String


FilePath = "X:\users\slef\2015\1 June\my doc 5.xls"
FileName = Mid$(FilePath, InStrRev(FilePath, "\") + 1)


MsgBox FileName


End Sub

Dave

 

[Rick Rothstein]

There's an easy guide at this page Extract Filenames from Filepath in Excel

Rather than use their formula, I think I would use this one instead...

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),300))

Also, instead of their multi-line subroutine, I think I would just use the coded equivalent of the manual method they posted...

Sub ExtractfilePath()
  ActiveSheet.Range("A1:A10").Replace "*\", "", xlPart
End Sub

 

[Rick Rothstein]

Also, instead of their multi-line subroutine, I think I would just use the coded equivalent of the manual method they posted...

Sub ExtractfilePath()
  ActiveSheet.Range("A1:A10").Replace "*\", "", xlPart
End Sub

Actually, since I am pretty sure Replace only works on the UsedRange, I would write the macro this way instead (ActiveSheet is the default, so I have eliminated it also)...

Sub ExtractfilePath()
  Columns("A").Replace "*\", "", xlPart
End Sub

 

[Claus8528]

Rather than use their formula, I think I would use this one instead...

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),300))

That is definitely a better solution! Thanks