Hi folks,

I am using Excel vba and need a dialog from the global collection of
build-in dialogs simply to select a directory. I know and can read in
the help that there is a huge list of these dialogs like used in the
operating system, but can't find the one to select a directory.
I searched also on MSDN on)line but no further documentation on the
dialogs, the parameters, sample ???

Can someone point me to a decent document upon these hidden treasures ?
Best regards,

Bernard

I never use vba and I dunno what this "global collection of built-in
dialogs" is in Excel. However, you can use the API to do the job. Add a User
Form and place a Command Button on it. Then paste the followinbg block of
code into the User Form:

Mike

Private Type BrowseInfo
  hWndOwner As Long
  pidlRoot As Long
  sDisplayName As String
  sTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
(bBrowse As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
(ByVal lItem As Long, ByVal sDir As String) As Long

Private Function Browse_Folder() As String
' Let the user browse for a folder. Return the
' selected folder. Return an empty string if
' the user cancels.
Dim browse_info As BrowseInfo
Dim lItem As Long
Dim sDirName As String
Dim hwnd As Long
browse_info.hWndOwner = hwnd
browse_info.pidlRoot = 0
browse_info.sDisplayName = Space$(260)
browse_info.sTitle = "Select Folder"
browse_info.ulFlags = 1 ' Return folder name.
browse_info.lpfn = 0
browse_info.lParam = 0
browse_info.iImage = 0
lItem = SHBrowseForFolder(browse_info)
If lItem Then
  sDirName = Space$(260)
  If SHGetPathFromIDList(lItem, sDirName) Then
    Browse_Folder = Left(sDirName, InStr(sDirName, Chr$(0)) - 1)
  Else
    Browse_Folder = ""
  End If
End If
End Function

Private Sub CommandButton1_Click()
Dim sfolder As String
sfolder = Browse_Folder
If sfolder = "" Then ' User Cancelled
  Caption = "No Folder Selected"
Else
  Caption = sfolder
End If
End Sub

Dear Mike,

Thank you for the sample.
I really wanted to avoid using additional dll's and the possible
problems of not being available in the field. So I searched somewhat
further and found next vba code to select a directory :

   Dim strTitle as string
   Dim strRetPath as string
   Dim objFolder As Object

   strTitle = "Select the folder where the documents fractions reside."
   Set objFolder = CreateObject("Shell.Application").BrowseForFolder(0,
strTitle, 0, ssfDESKTOP)

   If Not (objFolder Is Nothing) Then
      strRetPath = objFolder.Items.Item.Path
      Set objFolder = Nothing
   End If


Nice and Easy is it not ?
Regards,

Bernard

Berny,

If by using DLLs you mean Shell32.dll, then your code is depending on
it as well:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/shellcc/platform/shell/reference/objects/shell/browseforfolder.asp

Hope this helps,

Max Kudrenko
Brainbench MVP Program for Visual Basic
www.brainbench.com


Berny wrote:
Show quoteHide quote

I am anwering my own questions now  ;-)

Small correction in the line

strRetPath = objFolder.Items.Item.Path  ----> strRetPath =
ObjFolder.Self.Path

Then you can use it also in more weard places (like Desktop, etc...)
without error popping up

Bernard

Well if you think Shell32.dll is an "additional dll" that you don't really
need then remove it from your system and see what happens :-)

Mike

Mike,

I agree, Shell32.dll contains vital API functions and much more.

Bernard

Any known problems running VB6 application with Intel Centrino Duo
Windows or VB problem - Please help
MMTimer, DirectSound, and For..Next
test for empty array?
Runtime error 429