How to prompt users for a folder location in Excel VBA

If you are importing multiple files to Excel using VBA there are instances where it is preferable to prompt for a folder location rather than hardcode the location within the Excel VBA script. This post will show you what Excel VBA to use in order to prompt the user to select a folder containing data…

 

What is a folder prompt?
A folder prompt in Excel is a method that you can use to force the user to show Excel where the data or files live as part of a VBA script, it puts the emphasis on the user to show Excel where to get any files from but it is a great method as it is very simple to deploy and use, even for an Excel novice. If you are unsure of what a prompt is then consider when you want to open a file in Excel, you will be prompted by Excel to show where that file is, this method is basically a variation of that scenario:

00074_Excel VBA Prompt Folder_21042015_1

Why Prompt?
There are two main ways an Excel developer might import multiple files using VBA:

  • Hardcode the folder location: For example if the data is always stored in the folder ‘C:\My Documents\Data’ then you can set this in the VBA. You might choose this method when creating Excel workbooks where you are the sole user and know where to store the data files.
  • Prompt the user to show Excel where the data is: This is the most useful way when you are creating Excel files that will be shared to different users. Not all users will have the same file paths and folder names and they may not store the data in the correct folder even if they are required to, this method avoids all the hassle because every user will be prompted each time they run the script to locate the folder containing any relevant files.

If you are not sure which method is the right one to use then the safe option is to prompt, especially when you are sharing the Excel file with other users or one that may eventually be passed onto other users. In a business environment prompting users to select folders can avoid a lot of queries down the line and allow for an easier handover transition in the future so it should generally be considered the main choice.

 

 

How to Prompt with VBA
The VBA script to use for prompting the user to select a folder is below:

1
2
3
4
5
6
7
8
9
10
11
12
13
Dim flder As FileDialog
Dim foldername As String
Set flder = Application.FileDialog(msoFileDialogFolderPicker)
With flder
.Title = "Select the folder containing data"
.AllowMultiSelect = True
If .Show <> -1 Then GoTo NextCode
foldername = .SelectedItems(1)
End With
NextCode:
GetFolder = foldername
Set flder = Nothing
End Sub

 

What does the VBA do?
The script prompts the user to select the folder containing data using the FileDialog command and more specifically by telling Excel the MsoFileDialogType which is set to msoFileDialogFolderPicker. It is the last part that you need to pay attention to here as that tells you what type of command is being deployed, i.e. FolderPicker. There are other DialogTypes available, for example changing the DialogType to msoFileDialogFilePicker will allow the user to just select a single file which is useful for single file imports in Excel.
After we have opened up the FileDialog command it is a case of setting any options, in the example above these include changing the title of the Prompt box so that is tells the user to ‘select the folder containing data’.

00074_Excel VBA Prompt Folder_21042015_2
Again there are additional options to explore so you can customise to your needs but you will find this code covers 99% of all situations.
Lastly the VBA script example provided will set the filepath the user has selected to the named string foldername. By setting the path to a named string it allows you to reference that filepath throughout your VBA code just by using the named string ‘foldername’.
Summary

  • The Excel VBA script explained in this post can be used to prompt a user to select a folder containing multiple files for import into Excel.

 

  • With a little tweaking the script can also be used to prompt the user to select single files for import, to export data from Excel or even prompt the user asking where to save the file.

 

  • In a business environment, or any situation where multiple users will be running the script, it is generally best practice to use the prompt method for selecting a folder containing multiple files.

I hope you find this script useful in your existing and new VBA creations. If you have found a great or novel use for it then please send me a tweet to let me know, it’s always great to hear from other Excel users of all levels.

Keep Excelling,

Leave a Comment