
Please see the following code for using the folderpicker.
Using the following VBA class will give your projects a better feel for your end users as well as reduce the risk for user errors as we not relying on any cell reference.
MsoFileDialogType can be one of these constants:
msoFileDialogFilePicker. = Allows user to select a file.
msoFileDialogFolderPicker. = Allows user to select a folder.
Opening a Folder
Sub FolderPicker()
'Getting the FileDialog object.
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
'Opening the dialog. -1 means that it works.
If fDialog.Show = -1 Then
PathString = fDialog.SelectedItems(1) 'The full path to the folder or file selected by the user
End If
MsgBox (PathString)
End Sub
Opening a File
The below code lets you pick a file instead of a folder.
Please note the pink text parts. First one indicating a File, the others are some added functionality for a more pleasant UI.
Sub FilePicker()
'Getting the FileDialog object.
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Changes the upper left corner of the filedialog picker
.Title = "Select a folder"
'Changes the "OK" button text
.ButtonName = "Select a folder"
'Clears any previous filter setting should you have had that before.
.Filters.Clear
'Adding filter to my Filedialog view so that it only shows VBA files.
.Filters.Add "VBA files only", "*.xlsm", 1
End With
'Opening the dialog. -1 means that it works.
If fDialog.Show = -1 Then
PathString = fDialog.SelectedItems(1) 'The full path to the file selected by the user
End If
MsgBox (PathString)
End Sub
Getting all files from a folder
This a handy one I have been using a few times when I want to collect all files from a folder, in order to create attachments for emails.
Sub GetAllFilesInAFolder()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
FolderSheet.Activate
'**********First we are allowing the user to select the folder'********
'Getting the FileDialog object.
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
fDialog.Title = "Select a folder" 'Changes the upper left corner of the filedialog picker
fDialog.ButtonName = "Select a folder" 'Changes the "OK" button text
'Opening the dialog. -1 means that it works.
If fDialog.Show = -1 Then
PathString = fDialog.SelectedItems(1) 'The full path to the file selected by the user
End If
'Our Folder is now stored in PathString
'******END - First we are allowing the user to select the folder'******
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
If PathString <> "" Then
Set objFolder = objFSO.GetFolder(PathString) 'Opens function.
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
FilePath = objFile.Path 'filepath of each file.
'-5 below is just me removing the extension ".xlsx".
'If you're collecting other files from this folder I would suggest automating based on the "." instead.
Cells(i + 1, 1) = Mid(FilePath, 1, Len(FilePath) - 5)
i = i + 1
Next objFile
End If
MsgBox ("Done")
End Sub
Download Excel Workbook here.
Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.