Is there something like GetOpenFileName that would open a dialog box
and allow me to browse to a folder and pick it as the new Current
Directory?

I can use GetOpenFileName and pick a file in the correct directory and
that seems to change the CurDir( ). However, it isn't as intuitive and
clicking on a folder then pressing OK.

Thanks.

- John

After this post I discovered John Walkenbach's tip at
http://j-walk.com/ss/excel/tips/tip29.htm.

This seems to do the trick.

- John


John Michl wrote:
Is there something like GetOpenFileName that would open a dialog box
and allow me to browse to a folder and pick it as the new Current
Directory?

I can use GetOpenFileName and pick a file in the correct directory and
that seems to change the CurDir( ). However, it isn't as intuitive and
clicking on a folder then pressing OK.

Thanks.

- John

Here is one way. IXL2002 has a browse dialog.

With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)

End With

Look up FileDialog in the VBA help

The pre XL2002 way is

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John Michl" wrote in message
oups.com...
Is there something like GetOpenFileName that would open a dialog box
and allow me to browse to a folder and pick it as the new Current
Directory?

I can use GetOpenFileName and pick a file in the correct directory and
that seems to change the CurDir( ). However, it isn't as intuitive and
clicking on a folder then pressing OK.

Thanks.

- John

Thanks Bob. I browsed through the help looking for things such as
GetFolderOpen but never ran across FileDialog. I'll need check it out.
It is much simpler than my other approaches. Thanks!

- John


Bob Phillips wrote:
Here is one way. IXL2002 has a browse dialog.

With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)

End With

Look up FileDialog in the VBA help

The pre XL2002 way is

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John Michl" wrote in message
oups.com...
Is there something like GetOpenFileName that would open a dialog box
and allow me to browse to a folder and pick it as the new Current
Directory?

I can use GetOpenFileName and pick a file in the correct directory and
that seems to change the CurDir( ). However, it isn't as intuitive and
clicking on a folder then pressing OK.

Thanks.

- John