Really simple - VB code for the open file dialogue box?

[sebhaque]

Hi all.

Having a bit of an issue with Excel 101. I'm devising a nifty little macro that pulls in some data from a series of spreadsheets. These are all identical in layout so the actual legwork is very easy - but I'm struggling to remember how to call up the "open file" dialogue box (i.e. the one that you get if you press Ctrl+O).

What I'd like to do is, when the user presses a button, the box pops up allowing them to select the spreadsheet they want to grab the data from. They click OK, the workbook opens up, Excel switches to the opened workbook and does all the laborious copy and pasting.

Thanks in advance.

 

[VoG]

Try like this

Dim fname As Variant
fname = Application.GetOpenFilename()
If Not fname = False Then Workbook.Open (fname)

 

[sebhaque]

Thanks for the speedy response VoG. The code does display the open file dialogue box, but whenever I click on an Excel file (or any file for that matter) I receive a runtime error '424' - object required. The debug points to the Workbook.Open (fname) at the end of your code - which, hovering over it, is showing that fname is indeed the full filepath to the given document. Is there an alternative to the Workbook.Open command?

EDIT: Never mind. Just needed an 's' on the end of "Workbook" in "Workbook.Open"

Thanks again for your quick reply and assistance!

 

[VoG]

Apologies

Dim fname As Variant
fname = Application.GetOpenFilename()
If Not fname = False Then Workbooks.Open fname