Userform with File Selector Process not working

[DeanP]

Users need to browse for and select files from which certain worksheets have to be copied to another location,
and all of the worksheets combined into one worksheet.


In my active worksheet I have an active x control button that will run the code to do all the above when clicked.
I have created a userform that I've called the FILE SELECTOR TOOL that needs to pop-up when the control button is
clicked. In the there users will have to tell the macro what to do. (Refer attached sample of the form)
(a) click a browse button that will enable browsing in the directory where the files are located
(b) one a file is selected, the macro need to (preferably without opening the workbook) show a list in the selector
tool of the names of all the sheets in the workbook
(c) I want the users to be able to select the sheets that they want to be copied by highlighting them
(d) click on a command button "ok" will then copy the sheets to a location that is confirmed by the user following
a prompt


So far I have the code blow that enables the selection of the files. However, I need to refine it to seperate the
file path and copy that in one box and list the sheets in that file in the next box - and that is where I am stuck
- I can't find how to do that.

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFilePicker)
    With fldr
        .Title = "Select All Required Folders"
        .AllowMultiSelect = True
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(2)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

Any help would be greatly appreciated.

[Leith Ross]

Hello DeanP,

Here is one way to do it...

Sub BrowseForFiles()


    Dim File    As Variant
    Dim fldr    As Variant
    Dim n       As Long
    Dim sItem   As String
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Select All Required Folders"
            .AllowMultiSelect = True
            .InitialFileName = Application.DefaultFilePath
            If .Show <> -1 Then Exit Sub
            sItem = .SelectedItems(2)   ' Why only the second file?
        End With
    
        n = InStrRev(sItem, "\")
        fldr = Left(sItem, n)
        File = Right(sItem, Len(sItem) - n)
        
End Sub

[DeanP]

Hi Leith,

Thank you for your reply. This still does not list the filepath and sheet names in the textboxes. In fact nothing happens
after files are selected from the initial file name.

[Leith Ross]

Hello DeanP.

I just provided an example of how to separate the path from the file. Since your posted workbook contained neither ActiveX controls nor any macro code, I left the implementation of the example in your hands.

[DeanP]

Ok gotcha. Thank you so much. Your help is greatly appreciated.