How to choose a folder

How can I get the user of a macro to choose a folder?

The code I have now, opens a specific file. However I want the user to select a folder.

I want this as the macro is to open all the files in the selected folder...

My code now:

Application.Workbooks.Open Filename:= _
"X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs - Capital expenditure - comments\BFR " & Mnumb & " bud v1.xls" _
, UpdateLinks:=0

Here is some code that opens all files

Dim aryFiles
Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
End If
Next file

End Sub


--

HTH

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


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> How can I get the user of a macro to choose a folder?
>
> The code I have now, opens a specific file. However I want the user to
> select a folder.
>
> I want this as the macro is to open all the files in the selected
> folder...
>
> My code now:
>
> Application.Workbooks.Open Filename:= _
> "X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
> Capital expenditure - comments\BFR " & Mnumb & " bud v1.xls" _
> , UpdateLinks:=0
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=482149
>

http://www.j-walk.com/ss/excel/tips/tip29.htm
Selecting a directory

--
Regards,
Tom Ogilvy


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> How can I get the user of a macro to choose a folder?
>
> The code I have now, opens a specific file. However I want the user to
> select a folder.
>
> I want this as the macro is to open all the files in the selected
> folder...
>
> My code now:
>
> Application.Workbooks.Open Filename:= _
> "X:\Users\Shared\GENERAL\Christian S\05.10.28 - Budget packs -
> Capital expenditure - comments\BFR " & Mnumb & " bud v1.xls" _
> , UpdateLinks:=0
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=482149
>