Open Dialog Box

Posted by Ron on June 06, 2001 6:22 AM

I'm sure I'm just overlooking something, but I would like to have a command button that will open another excel file. I would like the Open Dialog box to open to a specific directory. I've started with the following code but am not sure how to open to the folder C:\Testing , and open the file the user chooses.
Thanks for any advice!

Private Sub cmdOpen_Click()

fileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

Posted by JAF on June 06, 2001 6:29 AM

You were almost there!

The code you need is as follows:
Sub Get_Data()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If
End Sub

Using the IF statement ensures that if the user clicks Cancel, your macro won't "fall over"

Hope this helps.

Posted by Ron on June 06, 2001 7:21 AM

Thanks for the help! One more question if I may...Is there a way for me to have the "Please choose a file to import" dialog open to a specific folder such as C:\Testing ??

Posted by JAF on June 06, 2001 8:42 AM

This should be what you need...

Sub Get_Data()
ChDrive "C:\"
ChDir "C:\Testing\"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If
End Sub

NB: The user will still be able to select another directory if they wish, but it will open by default to C:\Testing\ Thanks for the help! One more question if I may...Is there a way for me to have the "Please choose a file to import" dialog open to a specific folder such as C:\Testing ??

Posted by Ron on June 06, 2001 8:58 AM

Thanks...works great.

Thanks JAF!! I added the ChDrive & ChDir and it works!! This should be what you need... Sub Get_Data()