How to extract a file name from its path in vba

Question

I am writing a code, where I am opening a file for extracting data. I am currently using following code; I want to extract the file name from the path and store it in a particular range. This the code:

FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xls")
If FilePath <> False Then
Range("D6").Value = FilePath
file = Range("D6").Value
Range("D6").Clear
End If

You say "I want to extract the file name from the path and store it in a particular range" but then you clear D6. So do you want to do something with `file = Range("D6).value` later in your code **and** you want D6 to have just the file name without its path? — Mark Fitzgerald, Aug 18 '15 at 11:23
Yes exactly, I used this and it worked fine strName = Right(FilePath, Len(FilePath) - InStrRev(FilePath, "\")) then I assigned the strName to a Range and used the same value. — Ansh, Aug 19 '15 at 08:34

score 3 · Accepted Answer · answered Aug 18 '15 at 08:08

You can do it like below:

FilePath = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm")

If FilePath <> False Then
    Dim fso As Object
    Dim objFile As Object

    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    Set objFile = fso.GetFile(FilePath)

    If Not objFile Is Nothing Then
        FileName = objFile.Name
    End If

End If

score 3 · Answer 2 · answered Aug 19 '15 at 10:17

3

Simplest way:

FileName = Mid$(FilePath, InStrRev(FilePath, "\") + 1, Len(FilePath))

answered Aug 19 '15 at 10:17

Alex K.

171,639
30
264
288

score 2 · Answer 3 · answered Aug 18 '15 at 08:51

An alternative:

Public Function ExtractFileName(ByVal strFullName As String) As String

Dim p As Integer
Dim i As Integer
Dim s As Integer

i = 1
Do
    p = InStr(i, strFullName, "\", 1)
    If p = 0 Then Exit Do
    s = p
    i = p + 1
Loop
s = s + 1
ExtractFileName = Mid(strFullName, s, Len(strFullName))

End Function        'ExtractFileName

score 0 · Answer 4 · answered Dec 19 '18 at 11:59

If you want various version of the path and file names, here's what I would suggest:

Sub LookupFileNames()
  Dim FilePath As String, FileOnly As String, PathOnly As String, ExtOnly As String, NameOnly As String

  FilePath = ThisWorkbook.FullName
  FileOnly = ThisWorkbook.Name
  NameOnly = Left(FileOnly, InStr(1, FileOnly, ".") - 1)
  ExtOnly = Right(FileOnly, Len(FileOnly) - InStr(1, FileOnly, "."))
  PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))

  MsgBox "Full Name: " & FilePath & vbLf & "File Name: " & FileOnly & vbLf & "File Name w/o Ext: " & NameOnly & vbLf & "File Ext: " & ExtOnly & vbLf & "File Path: " & PathOnly

End Sub

How to extract a file name from its path in vba

4 Answers4

Linked