Copy File and ADD and NOT replace it

[Helen Pippard]

Hello

I was wondering if someone could help me with something

I need to be able to copy files and NOT replace them, and just add to the files each time

The files will always be aclled the same name, but the date modified will change each time and that is the part I know I need to concentrate on to get the file to copy and NOT replace the already existing file

At the moment what I have written below just replaces the file each time. How can I get the file to copy and not replace?

If FileDateTime("F:\Documents and Settings\user\My Documents\Query1.xls") >= FileDateTime("F:\Documents and Settings\user\My Documents\Misc\Query1.xls") Then fs.CopyFile "F:\Documents and Settings\user\My Documents\Query1.xls", "F:\Documents and Settings\user\My Documents\Misc\Query1.xls"

Any help or adavise would be very much apprecitated

Thanks
Helen

 

[Joe4]

Do you mean to post this to the Access forum?
Are you trying to do this via VBA in Access or Excel?

 

[Helen Pippard]

Hi

I meant for this to be on the acces forum and I am trying to attempt this in VBA

I think I need to look at the date modified part of the file in order to copy and not replace the already existing file

 

[Joe4]

I am not quite clear what you mean by "copy and add" and "not replace" the file. In order to do that, you would have to get the file a new/different name.

Or perhaps you mean open the Excel files and extract the data from the one and paste it to the end of the other Excel file?

If you can do this in an Excel macro, you could just call that Excel macro from Access VBA.

 

[Helen Pippard]

Hello

I have found the solution. I have put a date on the end of the string so that every time I copy a file the next file will show the date it was modified and therefore not replace the original file


fs.CopyFile "F:\Documents and Settings\user\My Documents\Query1.xls", "F:\Documents and Settings\user\My Documents\Misc\Query1" & "_" & Format(Date, "MM_DD_YYY") & ".xls"

Yay!

 

[Joe4]

Yep, that is what I meant when I said your would have to give the file a new name. Just note that your proposal will work as long as you only ever have to do this once a day. Otherwise, each subsequent daily run will overwrite the earlier one from that day. If you may have that situation, you may want to think about adding a time component as well, i.e.

...Format(Now(), "MM_DD_YYYY_HH_MM")