I am struggling to write up working code that prompts the user to select the folder path with FileDialog but cannot integrate it with another routine which imports the desired files to the open workbook. Below is my code so far but rather than pre-determining the folderpath I will 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

Any help is appreciated

CodePudding user response：

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) & "\"