Read Filename AND folder name in msofilepicker

[bmpreston]

I'm attempting to read the filename and folder name in a single action from a VBA that I managed to scrouge together from different messenging boards. What I have works, but requires two separate filepickers to open. This:

Sub FolderLocate()

Dim strFilePath As String
' Dim fs As Office.FileDialog

' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "https://foobar.com/sites/blah/blah/blah"

' show the file picker dialog box
If .Show <> 0 Then
strFilePath = .SelectedItems(1)

' *********************
' put your code in here
' *********************

' Example: print the path of the selected file to the immediate window
Range("AD2").Value = strFilePath
End If
End With

End Sub

Dumps the folder you select to AD2. This works

I then also select the file name:

Sub FileLocate()

Dim fd As FileDialog
Dim fName As String ' Includes full path
Dim fChosen As Integer
Dim fFolder As String 'Only the name of the file

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Please select file"
fd.InitialFileName = strFilePath
'"https://foobar.com/sites/blah/blah/blah/"
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), "/"))
'fFolder = Left$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "/"))
'Range("ad2").Value = fFolder
Range("AD3").Value = fName
Range("Q7").Formula = Range("AD1").Text
Range("Q9").Formula = Range("AD4").Text
End If



End Sub

This all works.

BUT, it is two separate actions, and if I comment out the beginning of the second, and make it one continuous script, it will run and select a folder on the first msoFilePicker, then a file on the second msoFilePicker, and I'm hoping to dump the file name in AD3, and the folder name in AD2 (smushing these 2 scripts together.) The commented "fFolder was my attempt but I simply don't understand how the Left$ and Right$ works, so I'm just purely guessing.

I also have 0 qualms of making a simple cell formula: dump the results, then parse it into an AD5 for example. I don't mind having commands hidden on the sheet.

Any assistance in parsing the folder name and file name out of these is super appreciated.

 

[Norie]

Change FolderLocate to a function that returns strFilePath and in the calling code split out the filename and folder.

 

[John_w]

Just use msoFileDialogFilePicker and:

fName = Mid(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\")+1)
fFolder = Left(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\"))

 

[bmpreston]

Thi

Just use msoFileDialogFilePicker and:

fName = Mid(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\")+1)
fFolder = Left(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\"))

This worked, I very much appreciate this response. The problem is that I am not strong with VBA at all. I am a script kiddie, stealing online code. Thank you for all you do!