How to prompt user to select file location in VBA to merge files into one workbook

user_17737254 0
Viewed 237

I want to prompt the user to select the folder path with FileDialog and integrate it with a routine which imports the desired files to the open workbook.

Below is my code but rather than pre-determining the folderpath I need to prompt the user:

Sub MergeCsvFilesToWb()

    Dim FolderPath As String
    Dim Filename As String
    Dim Sheet As Worksheet
    Dim fldr As FileDialog

    Application.ScreenUpdating = False
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select the Folder where the '.csv' files are located."
        .AllowMultiSelect = False
        .Show
    End With

    FolderPath = fldr.SelectedItems(1)
    Filename = Dir(FolderPath & "*.csv*")

    Do While Filename <> ""
        Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
            
        For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
            
        Workbooks(Filename).Close
        Filename = Dir()
        
    Loop
        
    Application.ScreenUpdating = True

End Sub
vba excel
1 Answers

The File/Folder selected in FileDialog can be found in the SelectedItems property (Documentation)

So to assign the variable FolderPath to the selected folder:

    Dim fldr As FileDialog
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select the Folder where the '.csv' files are located."
        .Show
        
        Dim FolderPath As String
        FolderPath = .SelectedItems(1)
    End With

Note that you should handle the event that the user does not select any folder (click Cancel) in the dialog so a better version would be:

    Dim fldr As FileDialog
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select the Folder where the '.csv' files are located."
        .Show
        
        Dim FolderPath As String
        If .SelectedItems.Count <> 0 Then
            FolderPath = .SelectedItems(1)
        Else
            'Code to handle event that nothing is selected
            'e.g.
            'Exit Sub
        End If
    End With

Lastly, the folder path returned does not have a slash at the end so you will need to either:

  1. Modify Filename = Dir(FolderPath & "*.csv*") to Filename = Dir(FolderPath & "\*.csv*")

  2. Modify FolderPath = .SelectedItems(1) to FolderPath = .SelectedItems(1) & "\"

user_155979360
Related