Selecting a Path in Excel 2000

I'm using Excel 2000 and I would like to bring up a dialog box that allows the user to select a Path. Does anyone have a solution for this? I've looked at a few API calls but none of them do specifically what I'd like...

Any ideas on how to accomplish this? Up until now I've been using either a textbox to type in the path or a file open dialog box and asking the user to open a file in the directory that wish to select.

Thanks,

Dave

Have you considered using the built-in "File Open" dialog box?
You can call this by using the dialogs collection of the excel application.
There are many arguments that you can use with the show method of the collection, including read-only, file type (.xls , .txt , etc.)....

VB Code:

Application.Dialogs(xlDialogOpen).Show

What you want is a BrowseForFolder dialog. You can create one out of all APIs very easy from this example here.

This will keep the user from getting confused with the OpenSave dialogs as they are prompting for a file to open or save.

VB Code:

'Modified example:
 
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 Command1_Click()
    '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, I haven't had a chance to try that yet but it sounds like what I am looking for! I'll have to figure out how to make API calls on my own some day. Any suggested reading (preferably free/sites but books are always an option) ?

I've been working on moving from C to C++ and am hopinig to work APIs and MFC into that as well, perhaps it'd be better to wait until I cross that line?

Check out allapi.net for allot of API tutorials and definitions.

You can find a list of most of them at www.allapi.net and also at MSDN On-Line.