PDA

View Full Version : Sleeper: Copy all files in a directory



cLYDE
06-17-2005, 03:39 AM
Hi,

Every month I have to copy the entire contents of a directory(12 woorbooks) from the head office directory on the network to my own directory also on the network.

From k:\fin\service\mothly reports\ohd reports\

to k:\fin\ops\distribution\ohdreports\

I have tried recording a macro while carrying out this routine but the macro is blank.

How can I write a macro to perform this funtion ?

Many Thanks,
Clyde

mvidas
06-17-2005, 05:20 AM
Hi Clyde,

The following macro should work fine for you! It won't process any opened files, but will tell you if any are so you can copy them manually afterwards. You didn't say if you wanted any filtering of the files ("June*.*", "*.xls", etc), if you're interested just let me know!


Sub cLYDECopyFiles()
Dim FromDir As String, ToDir As String, vFileName As String
FromDir = "k:\fin\service\mothly reports\ohd reports\"
ToDir = "k:\fin\ops\distribution\ohdreports\"
vFileName = Dir(FromDir)
Do While vFileName <> ""
If IsFileInUse(FromDir & vFileName) Then
MsgBox "Cannot copy " & FromDir & vFileName & ", file is being used."
Else
FileCopy FromDir & vFileName, ToDir & vFileName
End If
vFileName = Dir()
Loop
End Sub

Function IsFileInUse(ByVal vFile As String) As Boolean
Dim vFF As Long, ErrNo As Long
vFF = FreeFile
On Error GoTo AlreadyOpened
Open vFile For Binary Access Read Lock Read As #vFF
Close #vFF
IsFileInUse = False
Exit Function
AlreadyOpened:
IsFileInUse = True
End Function

Matt

Bob Phillips
06-17-2005, 05:27 AM
Every month I have to copy the entire contents of a directory(12 woorbooks) from the head office directory on the network to my own directory also on the network.
...
How can I write a macro to perform this funtion ?



Const DirFrom As String = "k:\fin\service\mothly reports\ohd reports"
Const DirTo As String = "k:\fin\ops\distribution\ohdreports"

Sub CopyFiles()
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FilesystemObject")
oFSO.CopyFolder DirFrom, DirTo
End Sub

Glaswegian
06-17-2005, 05:53 AM
Cross post here

http://www.mrexcel.com/board2/viewtopic.php?t=152272&sid=8e023ca0762eeaae505b7da46bbcc93c