Get Workbook reference from Open File dialog

Question

I'm using an Excel 2013 macro to extract data from a user-selected workbook and my vba is a bit rusty.

Application.GetOpenFilename prompts the user for a file location, opens the file and returns a string. Workbooks.Open(string) returns a Workbook - if you know the name in advance.

I want to combine these to ask the user which file to open, and return a Workbook.

Based on Frank's answer here (Open a workbook using FileDialog and manipulate it in Excel VBA) I've tried this:

Function openDataFile() As Workbook
'
  Dim wb As Workbook
  Dim filename As String
  Dim fd As Office.FileDialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.AllowMultiSelect = False
  fd.Title = "Select the file to extract data"
  'filename = fd.SelectedItems(1)
  Set wb = Workbooks.Open(fd.SelectedItems(1))
  openDataFile = wb

End Function

but this falls over on the commented line with Run-time error '5': Invalid procedure call or argument.

How do I prompt the user to open an excel file, and return a reference to it as a workbook?

Answer 1

Try the code below:

Function openDataFile() As Workbook
'
Dim wb            As Workbook
Dim filename      As String
Dim fd            As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the file to extract data"

' Optional properties: Add filters
fd.Filters.Clear
fd.Filters.Add "Excel files", "*.xls*" ' show Excel file extensions only

' means success opening the FileDialog
If fd.Show = -1 Then
    filename = fd.SelectedItems(1)
End If

' error handling if the user didn't select any file
If filename = "" Then
    MsgBox "No Excel file was selected !", vbExclamation, "Warning"
    End
End If

Set openDataFile = Workbooks.Open(filename)

End Function

Then I added the Sub below to test this function:

Sub test()

Dim testWb  As Workbook

Set testWb = openDataFile    
Debug.Print testWb.Name

End Sub