"excel vba open all csv files in a folder"?

For answering this inquiry we will mostly use the code for the folder picker in my other article where 90% of the code is already done for us that we will slightly modify. https://www.pls-fix-thx.com/post/open-file-folder-with-vba

Short background for the folder picker is that we can use it to pick either a folder or a file. This way we can let the user pick the folder for us where we want to find the latest saved file.

MsoFileDialogType can be one of these constants:

• msoFileDialogFilePicker. = Allows user to select a file.

• msoFileDialogFolderPicker. = Allows user to select a folder.

​Getting all files from a folder​

​In this case we will use the script "getting all files from a targeted folder" in order to find all of our available files. We can then test if their filetype to see if they are of filetype .csv, and if they are we can just open them in the loop so that all csv files are opened from the folder the user selects.

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
​
'*** Allowing the user to select the folder'***
'Getting the FileDialog object.
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
​
'Changes the upper left corner of the filedialog picker
fDialog.Title = "Select a folder" 
'Changes the "OK" button text
fDialog.ButtonName = "Select a folder"
​
    'Opening the dialog. -1 means that it works.
    If fDialog.Show = -1 Then
       'The full path to the file selected by the user
       PathString = fDialog.SelectedItems(1) 
    End If
 
'Our Folder is now stored in PathString

'*** END - 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.
        FileSave = objFile.DateLastModified 'modified date
        FileType = Mid(FilePath, InStrRev(FilePath, ".")
                   , Len(FilePath))
        
        
        'Checking if the File is of type .csv and if yes,
        'we open the file
        If FileType = ".csv" Then
            Open(FilePath)
        End If
        
        i = i + 1
        
    Next objFile
​
End If
​
MsgBox ("Done, all of our CSV files are opened.")
  
End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing the code for my previous article to get a jumpstart on this problem.

FolderPickerScript

.zip

Download ZIP • 24KB

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

If you have found this article or website helpful. Please show your support by visiting the shop below.

Pls Fix Thx Coffee Mug - Finance Blue

Buy Now