Cut and paste a file from one folder to another.

[gjsala]

How do I cut a .xls file from one folder and paste into another folder using vba? The criteria is to look at the file name which will have the date in the title (ex. 11_Jun_06_Jim.xls) and if it's greater that seven days from todays date move that excel spreadsheet to a different folder. There may be serveral files to move.

Thanks in advance!!

 

[rmikesmith]

gjsala,

You'll have to parse out the date portion of the filename, convert it to an actual date (CDate() function) then compare it to the result of the Date function + 7. Once the code finds a filename that meets the criterion, you can use the Name statement; Syntax: Name Oldpathname Newpathname. If you keep the same filename but supply a different folder path, it will move the file (does not work across drives, however). You could also use the Move or MoveFile methods of the FileSystemObject.


Hope this helps.
Mike

 

[CautionMP]

gjsala,
Not familiar with the Name statement, here is an ezample using the [tt]FileSystem[/tt], this is typed and untested.

Sub MoveOlderThan7Days()
Const SourceDir As String = "C:\"
Const DestinationDir As String = "C:\Backup\"
Dim strCurrentFileName As String, strTempDate As String
strCurrentFileName = Dir(SourceDir & "*.xls")
Do
  'assumes dd_mmm_yy
  strTempDate = Left(strCurrentFileName, 9)
  'replace the underscore to make it a date string
  strTempDate = Replace(strTempDate, "_", "-")
  If IsDate(strTempDate) Then
    If CDate(strTempDate) + 7 <= Date Then
      'FIleCOpy/Kill to avoid the FileSystemObject
      FileCopy SourceDir & strCurrentFileName, DestinationDir & strCurrentFileName
      Kill SourceDir & strCurrentFileName
    End If
  End If
  'Get the next file
  strCurrentFileName = Dir
Loop Until strCurrentFileName = ""
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[gjsala]

CautionMp,
Worked like a charm.

Thanks!!!

 

[gjsala]

From the code below I would like to have a different file type "*.pmp" instead of "*.xls":
strCurrentFileName = Dir(SourceDir & "*.xls")
Since it's not a "*.xls" file what changes do I need to make from this code? When I exchanged the .xls with .pmp and run the code the string name strCurrentFileName = "".

Thanks!

 

[gjsala]

Please disregard the last post. Here is the real question, I have a file "*.pmp" which I would like to have the code written in vba look at the modified date and if it's greater than two days of the actual date, move this file to a new location. There will be more than one file in this folder. I'm not sure where to change this in the following code:

Sub MoveOlderThan2Days()
Const SourceDir As String = "C:\"
Const DestinationDir As String = "C:\Backup\"
Dim strCurrentFileName As String, strTempDate As String
strCurrentFileName = Dir(SourceDir & "*.pmp")
Do
If IsDate(strTempDate) Then
If CDate(strTempDate) + 2 <= Date Then
FileCopy SourceDir & strCurrentFileName, DestinationDir & strCurrentFileName
Kill SourceDir & strCurrentFileName
End If
End If
'Get the next file
strCurrentFileName = Dir
Loop Until strCurrentFileName = ""
End Sub

Thanks!!!!

 

[CautionMP]

gjsala,
Something like this should work.

Sub MoveOlderThan2Days()
Const SourceDir As String = "C:\"
Const DestinationDir As String = "C:\Backup\"
Dim strCurrentFileName As String '[s], strTempDate As String[/s]
strCurrentFileName = Dir(SourceDir & "*.pmp")
Do
   [s]If IsDate(strTempDate) Then[/s]
    [s]If CDate(strTempDate) + 2 <= Date Then[/s]
    [b]If DateDiff("d", VBA.FileDateTime(strCurrentFileName), Date) > 2 Then[/b]
      FileCopy SourceDir & strCurrentFileName, DestinationDir & strCurrentFileName
      Kill SourceDir & strCurrentFileName
    End If
  [s]End If[/s]
  'Get the next file
  strCurrentFileName = Dir
Loop Until strCurrentFileName = ""
End Sub

If you don't want to use the [tt]DateDiff()[/tt] you could use simple math. This may produce different results, effectively checking for files more than 48 hours old:

...
    If Date - VBA.FileDateTime(strCurrentFileName) > 2 Then
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JerryKlmns]

A Name example

Name SourceDir & strCurrentFileName As DestinationDir & strCurrentFileName

which is a-long-story-short of: FileCopy + Kill

And works not only for cut+paste but also for renameing a file.