Dear all,

Need assistance in my macro for copying files from one directory t
another drive.

I have written the code:
Source = aFuturesDaily(i, 1) + aFuturesDaily(i, 2)
aFuturesDaily(i, 3)
Dest = aFuturesDaily(i, 5) + aFuturesDaily(i, 6) + aFuturesDaily(i
7)
FileCopy Source, Dest


One problem I have is that the source filenames always end with
random number. But the first part of the filename is fixed. Is ther
anyway to go around this problem? For example in DOS, you can use Cop
h:\test.*.csv G:\myfile.csv

Would appreciate some help!

Regards,
Desmon

--
Message posted from http://www.ExcelForum.com

Something like this :-

'-----------------------------
Sub test()
Dim MyName, FromPath, ToPath
'-----------------------------
FromPath = "C:\"
ToPath = "H:\"
MyName = Dir(FromPath & "test*.*")
Do While MyName < ""
FileCopy FromPath & MyName, ToPath & MyName
MyName = Dir
Loop
End Sub
'----------------------------

--
Message posted from http://www.ExcelForum.com

great stuff BrianB.

One more question.

How do I rename a filename? What's the command for that?

Thanks in advance

--
Message posted from http://www.ExcelForum.com

desmondleow < wrote:

How do I rename a filename? What's the command for that?

Sub test()
Name "c:\test1.csv" As "c:\test2.csv"
End Sub

<<How do I rename a filename?
You don't. exactly. You make a copy as above then delete the origina
with :-

Kill "c:\test1.csv

--
Message posted from http://www.ExcelForum.com

Loop through the Source directory and perform the same action action o
each of the files regardless of the name. You'll need to save eac
file using a unique name so that you don't overwrite the newly save
file each time, therefore, I have adding incrementing counter variabl
to append to the filename (vCounter). The code below grab each of th
files in the source directory and rename them as MyFile1.xls
MyFile2.xls, MyFile3.xls, etc. Just change the extension from .xls t
whatever extension you need.

NOTE: YOU MUST SET REFERENCE TO MICROSOFT SCRIPTING RUNTIME LIBRAR
FIRST!

Private Sub ReNameFiles()

Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim vCounter As Integer

Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder("C:\Source Directory")
vCounter = 1

For Each fsFile In fsDir.Files

fsFile.Copy ("C:\Destination Directory\MyFile" & vCounter & ".xls")
vCounter = vCounter + 1

Next

End Sub




Rolli

--
Message posted from http://www.ExcelForum.com