How to import a File or a Folder in to excel with VBA?

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.

FolderPickerScript

.zip

Download ZIP • 24KB