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.
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.