VBA to open an Excel File

Question

I am trying to open an excel file in a folder of excel files using VBA. I direct my code to take the end user straight to the folder and allow him to choose the file from the dialog box. But I am not able to open the file even after selecting it from the dialog box.

My understanding of the problem is - I am missing out on the Command to open the file after selecting it.

Here is my Code,

thisYear = Year(Date)


'change the display name of the open file dialog
    Application.FileDialog(msoFileDialogOpen).Title = _
    "Select Input Report"

 'Remove all other filters
 Application.FileDialog(msoFileDialogOpen).Filters.Clear

 'Add a custom filter
 Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
     "Excel Files Only", "*.xls*")

     'Select the start folder
     Application.FileDialog(msoFileDialogOpen _
     ).InitialFileName = "\\driveA\Reports\" & thisYear & ""

Please kindly share your thoughts. Thanks.

@Kyle I am able to select the file from the dialog box but still the file doesn't get opened. I wonder there is a separate command or a line of code to open the file that I am selecting from the dialog box. — Student of the Digital World
– Student of the Digital World, Commented Apr 25, 2017 at 21:29
Did you read the link and look at the related example? It shows you how to extract the path of the selected file, and then you can simply use Workbooks.Open() with that path. — Kyle
– Kyle, Commented Apr 25, 2017 at 21:32

M-- · Accepted Answer · 2017-04-25 21:47:30Z

2

I assume that you only let selecting one file (i.e. AllowMultiSelect = False).

 Dim file As String
 Dim myWbk As Workbook     

 file = Application.FileDialog(msoFileDialogOpen).SelectedItems(1) 

 Set myWbk = Workbooks.Open(file)

First line gets the path of the selected file and then second line opens it. Add this to the end of your code.

edited Apr 25, 2017 at 21:47

answered Apr 25, 2017 at 21:34

M--

33.6k12 gold badges74 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Student of the Digital World Over a year ago

Yes, Thanks @Masoud. I was looking to open a single file only.

Collectives™ on Stack Overflow

VBA to open an Excel File

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related