Code that will open the open dialog box for a specified folder

John Carney
John Carney used Ask the Experts™
on
i thought that the code below woyld bring up the open dialog box for the specified folder, but of course it doesn't. How do I do that?

Thanks,
John
ChDir "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something like this??

Sub Get_Data()
ChDrive "H:\"
ChDir "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks"
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
Ah, be sure to add the \ at the end of the New Weeks directory. Like this..

Sub Get_Data()
ChDrive "H:\"
ChDir "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks\"
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
Here's an example of the GetOpenFilename method:

Sub GetOpenFileName()
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer
    
    '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\Documents and Settings\All Users\Desktop\"
    '   Set File Filter
        Filt = "Excel Files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetOpenFileName(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open FileName

End Sub

Open in new window


You'll see a lot of parameters that you can change.

Hope that helps,
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Top Expert 2008

Commented:
Chris! :-)
Hi Gaberiel

please try with code
important code lines

' File filters
Filter = "Excel Files (*.xls),*.xls,"

' Select Start Drive & Path
ChDrive ("H")
ChDir ("H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks")

' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)

i hope this will surely helpful to you
by
kumaresan.v
Sub OpenSingleFile()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("H")
ChDir ("H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\Originals\New Weeks")
With Application
    ' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open File
Workbooks.Open Filename
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
End Sub

Open in new window

hi

this coding, u can open with multiple file also... please try out this...

by
kumaresan. v
Sub OpenMultipleFiles()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
'   Default filter to *.*
    FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("E")
ChDir ("E:\Chapters\chap14")
With Application
    ' Set File Name Array to selected Files (allow multiple)
    Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
    msg = msg & Filename(i) & vbCrLf ' This can be removed
    Workbooks.Open Filename(i)
Next i
MsgBox msg, vbInformation, "Files Opened"' This can be removed
End Sub

Open in new window

John CarneyReliability Business Tools Analyst II

Author

Commented:
Just got into work, I'll check this out as soon as I can.


- John
John CarneyReliability Business Tools Analyst II

Author

Commented:
Well the day got away from me pretty fast. I'm emailing myself to check it out first thing tomorrow :-)

John
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks.
John CarneyReliability Business Tools Analyst II

Author

Commented:
Sorry I didn't get back sooner since BlueThunda's posts answered the question completely. I gave some points to kumaresan2011 because his answer opened up some possibilities I was thinking about.

SmittyPro, pls forgive me for not including you but your answer was pretty much a duplicate of BlueThunda's and of course he got there first. But please take a look at a followup question I posted here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27044789.html. Anyone else as well.

Kevin, your answer as always was slyly brilliant in a zen master sort of way; so much so that many people might fail to recognize it as such.  You never disappoint. :-)

John
Kevin never ceases to amaze! :)

Heya!
Top Expert 2008

Commented:
I really appreciate the adoration and appreciation, but, truth be told, I was just saying hello and acknowledging my buddy Chris AKA SmittyPro. He doesn't show up often so I wanted him to know I saw his happy self. He does happen to be pretty good at doing whatever it is he decides to do on any given day - from code to teaching to botany.

:-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial