Solved: Open file path

[gscarter]

Hi,

Is there an equivalent to:



Application.GetOpenFileName()


for opening a folder path, rather than a file.

To explain what im trying to do:
I wish to open a browse window on the click of a button, put the folder path selected in a textbox, then read from that textbox later on to save a file there.

Thanks
Gary

[lucas]

Run the sub Test1 and change the msgbox to your textbox. You can change the start directory in the function......read the comments....hope this helps.
Option Explicit
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
' uncomment next line and put correct path in to start in specific folder
'OpenAt = "C:\Temp"
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename (file://\\servername\sharename). All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function

Sub test1()
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "an invalid folder!"
Case Else
'don't change anything
End Select
MsgBox "You selected " & result, _
vbOKOnly + vbInformation
End Sub

[Bob Phillips]

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

MsgBox .SelectedItems(1)
End If
End With

[lucas]

Good one Bob. I did notice that it opens initally to the excel default folder and then if you browse to a folder and select it and run it again it opens the last folder you browsed to...

[gscarter]

Thanks for all your help guys

[lucas]

Gary, be sure to mark it solved using the thread tools at the top of the page unless you have further questions.

[Bob Phillips]

Good one Bob. I did notice that it opens initally to the excel default folder and then if you browse to a folder and select it and run it again it opens the last folder you browsed to...

You can default it though



With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
.InitialFileName = "C:\test\"
If .Show = -1 Then

MsgBox .SelectedItems(1)
End If
End With


Note the \ at the end, very necessary.

[lucas]

That's clean and it defaults to My Documents if the path is invalid.......thanks for sharing it Bob.