Selecting an initial directory

[RWN]

xl2kPro
I'm looking for a "short" function that will allow the user to select an initial
directory.
My utility will then loop through the directory and subdirectories picking off specific
filenames (common extensions).
I have no problem with the second part (using the "Application.FileSearch") but cannot
find a function that will allow a box to popup for the initial directory selection.
I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that
requires a file to exist in the directory, which may/will not always be the case.
I could write my own routine to do this but was hoping for an "easier" way-in the deep
dark (*very*) distant past I remember having to do this via an API call.

Any suggestions, I've reached the end of the object browser!

 

[Rowan Drummond]

I think you still need to use API or Shell commands. See:

http://www.cpearson.com/excel/BrowseFolder.htm

Hope this helps
Rowan

 

[Bob Phillips]

XL2002 has a browse dialog. I don't use 2002 myself, so you may need to play
with this, but this is basically it

With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)

End With

Look up FileDialog in the VBA help


--

HTH

RP
(remove nothere from the email address if mailing direct)

xl2kPro
I'm looking for a "short" function that will allow the user to select an initial
directory.
My utility will then loop through the directory and subdirectories picking off specific
filenames (common extensions).
I have no problem with the second part (using the

"Application.FileSearch") but cannot

 

[Mike Fogleman]

'The pre XL2002 way is below. Just append the filename to the returned
'folder.

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


Mike F

 

[Bob Phillips]

OP said .... xl2kPro

That is why I bothered posting after Rowan's response.

Bob

 

[Bob Phillips]

Ignore me, I am cracking up.

Bob

'The pre XL2002 way is below. Just append the filename to the returned
'folder.

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


Mike F

 

[RWN]

Whew! Thought it was me!

Thanks to all for enlightening me.


Rob
------------------------------------------------------------------------

Ignore me, I am cracking up.

Bob

<snip>