Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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")


Check out our Excel VBA Resources

Re: Open Dialog Box

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.


Re: Open Dialog Box/Thanks & Additional question....

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 ??



Re: Open Dialog Box/Thanks & Additional question....

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 ??


Thanks...works great.

Posted by Ron on June 06, 2001 8:58 AM
Thanks JAF!! I added the ChDrive & ChDir and it works!! This should be what you need... Sub Get_Data()


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.