This is a simple demo of how you can have the user browse for a folder:

Sub ShowBrowseForFolder()
Dim Fld As Object
Set Fld = CreateObject("Shell.Application").BrowseForFolder( 0, "Select
source folder", 0, "c:\")
If Not Fld Is Nothing Then
MsgBox Fld.Self.Path
End If
End Sub

This dialog will have a Make New Folder button on it. If you don't want
that to appear replace the second zero with 512.

--
Jim
"saybut" wrote in
message ...
|
| Hi,
|
| I was wondernig if anyone could help me...
|
| I have a macro which was written a while ago with some assitance from a
| member of excelforum.
|
| Basically it looks in one directory, and copies the files listed in
| column A to anther directory. The code is below.
|
| I'm looking now to expand this macro so that when I click the button
| "copy files", a file browser window would open so that I can select the
| relevant directory rather than changing it in script everytime I want to
| look at a different folder. Ideally I'd have the same thing for the copy
| destination.
|
| If anyone could help it would be great, even to point me in the right
| direction for getting vb to open a file browser.
|
| macro:
|
| Sub copy_files()
| Dim rng As Range
| Dim cell As Range
| Dim SrcFilename As String
| Dim DestFilename As String
|
| Set rng = Selection
| For Each cell In rng
| SrcFilename = "\\network_location\" & cell.Value 'change source path
| DestFilename = "\\network_location\" & cell.Value 'change destination
| Path
| FileCopy SrcFilename, DestFilename
| Next
| End Sub
|
|
|
| Many thanks,
|
| Mark.
|
|
| --
| saybut
| ------------------------------------------------------------------------
| saybut's Profile:
http://www.excelforum.com/member.php...fo&userid=5949
| View this thread: http://www.excelforum.com/showthread...hreadid=395820
|