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?

try the code in my answer below – Shai Rado Sep 14 '16 at 08:20 — Shai Rado, Sep 14 '16 at 08:20

Shai Rado · Accepted Answer · 2016-09-14T08:28:52.823

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

Sold! Interestingly, I couldn't do `Workbooks("nameFromFileDialog").Activate` which originally prompted the question. I was expecting `Workbooks(workbookReference).Activate` to work, but it seems I need `Workbooks(workbookReference.Name).Activate`. So now I don't get why the string I originally tried doesn't work, but the workbook name string does ... (but i'm not going to lose any sleep over it :) — mcalex, Sep 14 '16 at 08:38

score 0 · Answer 2 · answered Sep 14 '16 at 08:19

Looks like you haven't shown the FileDialog so maybe something like 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"
  fd.show
  On Error Resume Next ' handling error over the select.. later in the script you could have an `if fileName = "" then exit sub` or something to that affect
  fileName = fd.SelectedItems(1)
  On Error GoTo 0
  Set wb = Workbooks.Open(fileName)
  openDataFile = wb

End Function

My code does show the FileDialog. I think the reason for using .show is to see if the filedialog returns true or false. — mcalex, Sep 14 '16 at 08:40

Get Workbook reference from Open File dialog

2 Answers2