Then perhaps you should scrap my original method and use something by Chip
Pearson that has an option for a starting folder.
First, you must set a reference to the "Microsoft Shell Controls And
Automation" library. In VBA, go to the Tools menu, choose References, and
scroll down in the list to "Microsoft Shell Controls And Automation" and
check the checkbox. Then, copy the following code into a standard code
module.
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String
Dim SH As Shell32.Shell
Dim F As Shell32.Folder
Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If
End Function
You can call the function above with code like the following:
Dim FName As String
FName = BrowseFolder(Caption:="Select A
Folder",InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
Debug.Print "No folder selected."
Else
Debug.Print "Folder Selected: " & FName
End If
Mike F
The following is a Function that will bring up a browser window to select
your folder:
Option Explicit
'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
Hi Mike,
I'm using this routine and it works perfect, but i still have a
question.
The folder path i want to select (most cases) is a 'base' path and a
subdirectory in the base path.
example of base path : \\bvwsrv01\BVW\AV\Operations\HASS\2.Utility and
Log file\ErrorDataBase Logging\
sub path : KDU1080
Result = \\bvwsrv01\BVW\AV\Operations\HASS\2.Utility and Log file
\ErrorDataBase Logging\KDU1080
Is it possible to 'preset' the base path, so that i dont have to start
searching form the Desctop up to where i need to be?
Any help welcome.
Regards and thanks a lot for the help
Ludo
