Excel 97 browse facility for folders

[davepirie]

I've looked at the xlDialogs and none of them seem to give an output that can be used to return a folder path. It also appears that treeview is incompatible with Excel 97.

Does anyone have any ideas on how I might accomplish giving the user the interactive ability to select a folder path that is then used in the code.

 

[DaleWatson123321]

Dave,

Here's an option...

1) For a prominent cell on your main user-interface sheet, identify the cell (perhaps with a text box next to the cell) as being the place for the user to specify the path to be used.

2) Assign a range name to the cell. In the following example code, I've used the range name "pth".

3) Example code...

Sub Set_Path()
path_ = Range("pth&quot.Value
ChDir path_
End Sub

I hope this can be useful. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jcrater]

Insert the following into a module.

Option Explicit

Public 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

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Public Function GetDirectory(Optional Msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, X As Long, pos As Integer

' Root folder = Desktop
bInfo.pidlRoot = 0&

' Title in the dialog
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = Msg
End If

' Type of directory to return
bInfo.ulFlags = &H1

' Display the dialog
X = SHBrowseForFolder(bInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal X, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function