VBA Get File Name From Path and Store it to a Cell

Question

I'm working on some code that I would like to find the path of a selected file, extract the file name, and then write the file name to a cell on the sheet. Here's what I have so far:

Private Sub CommandButton3_Click()

Sheets("Raw Data").Unprotect

Application.DisplayAlerts = False
Sheets("Raw Data").Delete
Sheets.Add After:=Worksheets(1)
Worksheets(2).Name = "Raw Data"
Application.DisplayAlerts = True

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim n As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

    SaveDriveDir = CurDir
    MyPath = "H:"
    ChDrive MyPath
    ChDir MyPath

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", MultiSelect:=True)
    If IsArray(FName) Then
        Application.ScreenUpdating = False
        Set basebook = ThisWorkbook
        For n = LBound(FName) To UBound(FName)
            Set mybook = Workbooks.Open(FName(n))
            Set sourceRange = mybook.Worksheets(1).Cells
            SourceRcount = sourceRange.Rows.Count
            Set destrange = basebook.Sheets("Raw Data").Cells
            sourceRange.Copy destrange
            mybook.Close True
        Next
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
    Application.ScreenUpdating = True

    Sheets("Main").Select
    Cells(5, 4).Value = FName

    Sheets("CS-CRM Raw Data").Select
    ActiveSheet.Cells(1, 1).Select

Sheets("Raw Data").Protect

End Sub

So far the code will get the path from this line:

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", MultiSelect:=True)

And it will write it to a cell with these lines:

Sheets("Main").Select
Cells(5, 4).Value = FName

However, every time I try to get it to just get the file name it doesn't work. I'll either get an error message or it will just post the entire path again. Does anyone know the best way to do this?

Thanks, I haven't seen that thread. – user3783788 Jul 30 '14 at 14:27 — user3783788, Jul 30 '14 at 14:27

score 2 · Answer 1 · answered Jul 30 '14 at 14:36

2

Here is a way to parse the result of GetOpenFileName() into three parts:

path
filename
file extension

..

Sub qwerty()
    Dim f As String, Path As String, _
        FileName As String, FileType As String

    f = Application.GetOpenFilename()

    ary = Split(f, "\")
    bry = Split(ary(UBound(ary)), ".")
    ary(UBound(ary)) = ""
    Path = Join(ary, "\")
    FileName = bry(0)
    FileType = bry(1)

    Range("A1") = Path
    Range("A2") = FileName
    Range("A3") = FileType
End Sub

For example:

qwerty

answered Jul 30 '14 at 14:36

Gary's Student

95,722
10
59
99

In my code I have FName defined as a variant, where you have your variable f defined as a string. Would I need to change anything with FName to use this code, or is it interchangeable with what you have as f? – user3783788 Jul 30 '14 at 14:44
You should have no problem...........try it out. – Gary's Student Jul 30 '14 at 14:45
If I try to use this code with FName substituted for f I get a type mismatch error. Sorry if this is an easy fix I'm still in the process of learning VBA. – user3783788 Jul 30 '14 at 15:04

Kokkie · Answer 2 · 2014-07-31T04:52:04.967

You should also bear in mind that they could select more than 1 file;

Sub getfilenames()
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
    MultiSelect:=True)

    i = 1
    For n = LBound(FName) To UBound(FName)
        FnameInLoop = Right(FName(n), Len(FName(n)) - InStrRev(FName(n), _
        Application.PathSeparator, , 1))
        Cells(i, 4).Value = FnameInLoop
        i = i + 1
    Next n
End Sub

VBA Get File Name From Path and Store it to a Cell

2 Answers2

Linked