VBA filepicker, file name only.

[bmpreston]

I have a VBA script found on this site which ironically worked, and then it stopped...

I admittedly do not know how this works, as I can't barely parse out VBA, but the purpose of this is to find just the file name for a msofilepicker that runs. When I then value a cell to the variable fname, I get the entire filepicker name AND location.

Any assistance?

Thanks

Sub ChooseFile()

    Dim fd As FileDialog
    Dim fName As String  ' Includes full path
    Dim fChosen As Integer
    Dim fNameFile As String  'Only the name of the file
   
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
            fd.Title = "Please select file"
            fd.InitialFileName = "C:\Users\xx\Downloads\CSVdata*"
            fChosen = fd.Show
                    fd.Filters.Clear
                    fd.Filters.Add "CSV files", "*.csv"
    
         If fChosen <> -1 Then
             MsgBox "You Cancelled, nothing done"
         Else
            fName = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))
range("t5").value = fName
        End If

End Sub

 

[rollis13]

Seems to me that your macro is in working condition, it deliveres in cell T5 only the name of the file.
Anyway, I would make some changes to fix what I think is a typo and to have the FileDialog showing only .csv files.

'...
fd.InitialFileName = "C:\Users\xx\Downloads\CSVdata\" '<= was ..\CSVdata*"
fd.Filters.Clear
fd.Filters.Add "CSV files", "*.csv"
fChosen = fd.Show                             '<= moved down
'...

 

[bmpreston]

Seems to me that your macro is in working condition, it deliveres in cell T5 only the name of the file.
Anyway, I would make some changes to fix what I think is a typo and to have it showing only .csv files.

'...
fd.InitialFileName = "C:\Users\xx\Downloads\CSVdata\" '<= was ..\CSVdata*"
fd.Filters.Clear
fd.Filters.Add "CSV files", "*.csv"
fChosen = fd.Show                             '<= moved down
'...

I can agree. I had to comment out some of it, I didn’t post it as I have it. I posted as I found it in the forums.
But strangely in my workbook, I get the entire file name. (Folders and all).
Thanks

 

[rollis13]

No other idea at the moment, not sure but if you are using a Mac maybe you should be using "/" instead of "\" in this line of code:
fName = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))

 

[bmpreston]

No other idea at the moment, not sure but if you are using a Mac maybe you should be using "/" instead of "\" in this line of code:
fName = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))

Ironically, you nailed it. It's not a Mac, however I'm using a Sharepoint site. teams.foobar.com/sites/sharepoint/asdfasdf/asdfasdf/asdfasdf.xlsm

Thank you!

 

[rollis13]

I'm glad I've been of some help?.