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

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
|