Here is a little function that returns the selected 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"broogle" wrote in message
oups.com...
This code below will count the total of files in c:\myfiles\
Is there a way to change the folder as dynamic, so user is free to
chose the folder she/he wants.
What I want is, when the code is running the pop up box will be shown
and user can browser to find the folder and when OK button is pressed
the code will count the files in that folde.
Thank and appreciate your help.

Private Sub countfiles()

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Myfiles\"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Range("A" & i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub