Processing
 

Get Workbook reference from Open File dialog

14/09/2016 08:04#1

mcalex

Member

Joined at: 8 months ago

Post: 4

Thank: 0

Thanked: 0

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?

14/09/2016 08:14Top#2

Shai Rado

Member

Joined at: 7 months ago

Post: 3

Thank: 0

Thanked: 0

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 
14/09/2016 08:19Top#3

Jeremy

Member

Joined at: 11 months ago

Post: 89

Thank: 0

Thanked: 0

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 
Similar articles

Excel More conditions in one formula?

5 months ago - Reply: 2 - Views: 172

Get Workbook reference from Open File dialog

5 months ago - Reply: 2 - Views: 130

Add Calculated Field To Pivot Table

6 months ago - Reply: 0 - Views: 7

Nested IF Functions in Excel not working

6 months ago - Reply: 3 - Views: 164

Formulating Date in Excel 2013

6 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

6 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

6 months ago - Reply: 1 - Views: 23

excel search and copy in/from another file

6 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

6 months ago - Reply: 0 - Views: 6

Excel static conditional formatting area

6 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re