Copying files

[charlee63]

Trying to copy files from one location to another.
Sample
c:\Indept Ad\01-02-11 Meat Merchandiser\01-02-11 Meat Merchandise.xls
Moving to
C:\2011 Temp\
Also need to change the file Name to 2011-01-02_East_Meat_Merchandise.xls

Multi folders Date changes in incumets of 7
Need to do to current Date.

 

[Trevor G]

Take a look at this link it might help.

A simple sample of code to copy a workbook to another location is shown below

http://www.rondebruin.nl/folder.htm

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copyfiles()<br><SPAN style="color:#00007F">Dim</SPAN> srcWk <SPAN style="color:#007F00">'The original</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cpyWk <SPAN style="color:#007F00">'Copy to new location</SPAN><br>srcWk = "M:\Excel Files\Hide.xls"<br>cpyWk = "M:\Excel Files New\hide.xls"<br>FileCopy srcWk, cpyWk <SPAN style="color:#007F00">'Using the FileCopy command</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Ruddles]

If you have trouble with the code for handling the dates, you could try (something like) this:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub CopyDatedFile()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim TodaysDate As String
  Dim SourceFile As String
  
  Dim NextWeeksDate As String
  Dim NextWeeksYear As String
  Dim TargetFile As String
  
  TodaysDate = Format(Now(), "dd-mm-yy")
  SourceFile = "C:\Indept Ad\" & TodaysDate & " Meat Merchandiser\" & TodaysDate & " Meat Merchandise.xls"
  
  NextWeeksDate = Format(Now() + 7, "yyyy-mm-dd")
  NextWeeksYear = Format(Now() + 7, "yyyy")
  TargetFile = "C:\" & NextWeeksYear & " Temp\" & NextWeeksDate & "_East_Meat_Merchandise.xls"
  
  FileCopy SourceFile, TargetFile
  [/FONT]
[FONT=Fixedsys]End Sub[/FONT]

There's no checking in this code, so you may wish to:-

• check the source file exists before trying to copy it
• check that the target folder exists before trying to copy the source file into it
• check the target file doesn't exist before overwriting it
• check that the target file exists after the copy operation