I am developing a userform that will allow a user to change thie working
directory for a particular project. Does anyone know if there is something
similar to getfileopen that will return a directory rather than a
file?Alternatively does anyone have any bright ideas how I could use
getfileopen to achieve my desired result?

Regards

Gordon Rodman

Gordon,

Here's a little function that you can incorporate in your code

Option Explicit

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 UDTs
'---------------------------------------------------------------------------
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.")
'-------------------------------------------------------------
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

path = Space$(512)

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

End Function


To invoke it, you just run like

MsgBox GetFolder()

This will return your selected folder, or blank if you cancel.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Rodman" wrote in message
...
I am developing a userform that will allow a user to change thie working
directory for a particular project. Does anyone know if there is something
similar to getfileopen that will return a directory rather than a
file?Alternatively does anyone have any bright ideas how I could use
getfileopen to achieve my desired result?

Regards

Gordon Rodman

I use a variation of this tip from John Walkenbach to select a
directory.

http://j-walk.com/ss/excel/tips/tip29.htm

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
On Tue, 23 Sep 2003 16:04:52 +0000 (UTC), "Gordon Rodman"
wrote:

I am developing a userform that will allow a user to change thie working
directory for a particular project. Does anyone know if there is something
similar to getfileopen that will return a directory rather than a
file?Alternatively does anyone have any bright ideas how I could use
getfileopen to achieve my desired result?

Regards

Gordon Rodman

And if you're using xl2002, take a look in VBA's help for:

Application.FileDialog

There's an example using: msoFileDialogFolderPicker



Gordon Rodman wrote:

I am developing a userform that will allow a user to change thie working
directory for a particular project. Does anyone know if there is something
similar to getfileopen that will return a directory rather than a
file?Alternatively does anyone have any bright ideas how I could use
getfileopen to achieve my desired result?

Regards

Gordon Rodman

--

Dave Peterson

Many thanks to you all for your help.

"Gordon Rodman" wrote in message
...
I am developing a userform that will allow a user to change thie working
directory for a particular project. Does anyone know if there is something
similar to getfileopen that will return a directory rather than a
file?Alternatively does anyone have any bright ideas how I could use
getfileopen to achieve my desired result?

Regards

Gordon Rodman