File Dialogs????

[bujin]

How do I use File Dialog Boxes in Excel through VBA? I have a command button on a worksheet that, when clicked, I want to display a File Open dialog box, get the filename and load the (Excel) file. How can I do this?

Thanks

 

[DarkSun]

There are two methods:

1. Application.Dialogs(xlDialogOpen).Show
This opens the selected file automatically.

2. Application.GetOpenFilename
this returns the filename for you to do what you want. eg. open file.

 

[bujin]

Thanks!

 

[bujin]

As an addition to this question, now that I have returned a filename, using Application.GetOpenFilename, how can I split this string up into the Path and FileName?

 

[DarkSun]

I think the easiest way is this:

1. the path will be the Current Directory so you can use Filesystem.CurDir (this may change later in your program so set a variable to this value straight after the GetOpenFilename)

2. to get the filename just take the right of the entire string as shown below.

MyFile = Application.GetOpenFilename
MyPath = FileSystem.CurDir
MyFilename = Right(MyFile, Len(MyFile) - Len(MyPath) - 1)

That should do it.

 

[bujin]

Cheers mate. Worked beautifully!