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

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" wrote in
message ...

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" wrote in
message ...

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