How to import selected worksheets using FileDialog?

Question

I'm currently trying to assemble a master workbook which will import data from other files. The user should be able to select a file using the FileDialog which works fine so far. What's currently not working is trying to import a worksheet which the user should be able to choose from in the userform. As far as my tests have gone so far there are two options:

Put two ComboBoxes on the UserForm with static values in each one (one for the filename and one for worksheetnames). Since the Import-Sheets will always be a selected type and the worksheets should all have the same name in the future that could work, but only if those conditions will be met throughout.
One ComboBox on the UserForm which shows the worksheet names of the workbook to be imported (since I'll have to open that anyway to copy the values over to the master workbook, it should be possible to parse the worksheet names into the ComboBox).

Is what I'm trying to achieve even possible using VBA?

So far I haven't been able to get the worksheet names into the ComboBox and import the data.

Yes it is achievable - you can open the selected Workbook and then loop through the available sheets in the WB. While doing so you add each sheet name to the combobox. — therak

therak therak · Accepted Answer · 2015-11-05T09:22:58

Here a short example how to do so:

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    'Show and check whether file is selected
    If .Show Then
        'you might wanna check if an excel workbook has been selected, or filter the filedialog upfront
        Dim wbSource As Workbook
        Dim ws As Worksheet
        'Open Workbook
        Set wbSource = Application.Workbooks.Open(.SelectedItems(1))
        'Loop through the available sheets
        For Each ws In wbSource.Sheets
            MsgBox ws.name
        Next

    End If
End With

This should get you started - instead of displaying the name just add it to your combobox. Afterwards you can proceed with importing the sheet throug copy&paste

How to import selected worksheets using FileDialog?

1 Answers