Read Filename AND folder name in msofilepicker

bmpreston

Board Regular
Joined
Jun 18, 2016
Messages
113
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Change FolderLocate to a function that returns strFilePath and in the calling code split out the filename and folder.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
Just use msoFileDialogFilePicker and:
VBA Code:
fName = Mid(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\")+1)
fFolder = Left(fd.SelectedItems(1), InStrRev(fd.SelectedItems(1), "\"))
 
Solution

bmpreston

Board Regular
Joined
Jun 18, 2016
Messages
113
Thi
Just use msoFileDialogFilePicker and:
VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top