top of page

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


open file folder 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.


49 views1 comment

1 comentario


CBKM BOCU
CBKM BOCU
03 nov

EPTU Machine ETPU Moulding…

EPTU Machine ETPU Moulding…

EPTU Machine ETPU Moulding…

EPTU Machine ETPU Moulding…

EPTU Machine ETPU Moulding…

EPS Machine EPS Block…

EPS Machine EPS Block…

EPS Machine EPS Block…

AEON MINING AEON MINING

AEON MINING AEON MINING

KSD Miner KSD Miner

KSD Miner KSD Miner

BCH Miner BCH Miner

BCH Miner BCH Miner

Me gusta
bottom of page