I need a macro to return the path to the folder file is in

[Mech_Eng1]

I have a macro that currently asks the user to enter the path to a folder containing files and it than takes out the files one by one to work with them. I would like to change the macro so that the user can search for the folder containing the files, something like a browse button, but it needs to return the file path not the actual file. Any Idea's on how to solve this problem would be appreciated.

 

[Mech_Eng1]

Maybe this will help make the question more clear.
Here is the code I currently have:
Directory = InputBox("Enter complete filepath for Boat *n.000 files")
Rather than have someone enter the file path I would like to have them browse for one. Is that possible to do somehow?

 

[Mech_Eng1]

Still Hoping someone out there has some ideas for me

 

[xenou]

***From Chip Pearson's very valuable site***
http://www.cpearson.com/excel/BrowseFolder.aspx

Paste this into a standard module and run the myBrowseForFolder macro. The declarations must be before any routines or functions in the module. Presumably you could use fName (file folder name) after it is returned by this macro.

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

Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long


Function BrowseFolder(Optional Caption As String = "") As String
'From Chip Pearson's website
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
   .hOwner = 0
   .pidlRoot = 0
   .pszDisplayName = String$(MAX_PATH, vbNullChar)
   .lpszINSTRUCTIONS = Caption
   .ulFlags = BIF_RETURNONLYFSDIRS
   .lpfn = 0
End With

FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
   Res = SHGetPathFromIDListA(ID, FolderName)
   If Res Then
       BrowseFolder = Left$(FolderName, InStr(FolderName, vbNullChar) - 1)
   End If
End If

End Function
Sub myBrowseForFolder()

'You can then call the BrowseFolder function with code like the following:

Dim FName As String
FName = BrowseFolder(Caption:="Select A Folder")
If FName = vbNullString Then
    MsgBox "No Folder Selected"
Else
    MsgBox FName
End If
End Sub