Select directory

Can anyone help me with VBA in Excel please.

I want the user to be able to select a directory where files are to be saved, or add a new directory which is then created. I have looked at the Common Dialog Control but it is not suitable as it selects a file. Is there anything similar for directories or can someone suggest another method.

As I am a novice, sample code would be much appreciated.

Thanks

With microsoft sysinfo control you get dirlistbox and drivelistbox controls.

With those you can handle drives and folders.

Even for novices the best way to learn is to get tips and break by yourself :)

Here is an example that I'm sure you will find useful.
You can only see directories and not any files.

VB Code:

Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
Const BIF_RETURNONLYFSDIRS = 1
Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Private Sub Form_Load()
    'KPD-Team 1998
    'URL: [url]http://www.allapi.net/[/url]
    '[email protected]
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo
 
    With udtBI
        'Set the owner window
        .hWndOwner = Me.hWnd
        'lstrcat appends the two strings and returns the memory address
        .lpszTitle = lstrcat("C:\", "")
        'Return only if the user selected a directory
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
 
    'Show the 'Browse for folder' dialog
    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        'Get the path from the IDList
        SHGetPathFromIDList lpIDList, sPath
        'free the block of memory
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If
 
    MsgBox sPath
End Sub

Thanks Jamo,

microsoft sysinfo control sounds like the controls I have come across in VB and they are fairly easy to use, but I can't find them in Excel VB.


RobDog888,

Thanks for the code, can't say I can understand it, but pasted it into my Excel workbook in ThisWorkbook and when I tried to run it I got 'Compile error - method or data member not found' where you set the owner window on the .hwnd.

Created a form and put it there and still get same message. Is there a reference I need to tick in Tools, References?

Sorry, this really is above me at the mo.

Thanks for your help guys.

The owner shouldn't be an issue, as most API's that ask for it (and don't directly affect a form) are happy with it not being set.

You can either set it to a hWnd if you have one (eg: Me.hWnd, or Form1.hWnd), or you can set it to 0.

Sorry for that. I forgot to change it for Excel. The line
".hWndOwner = Me.hWnd" should be ".hWndOwner = Application.Hwnd".

HTH

:thumb:

Thanx RobDog888,

Changed code as suggested and it works fantastic.
Sorry too so long to get back, due to workload.

Thanks for all your advice.