Copying Last month's files to a different location

[everblazing]

Hi

I am trying to build a macro that will move last month's files to a different location.

files names are BDP_171002.CSV to BDP_171031.CSV. These files are generated based on weekdays.

I want to move files based on last month file names NOT date modified. what do i need to adjust on my macro below.

Appreciate any help.

Sub Copy_Files_Dates()'This example copy all files between certain dates from FromPath to ToPath.
'You can also use this to copy the files from the last ? days
'If Fdate >= Date - 30 Then
'Note: If the files in ToPath already exist it will overwrite
'existing files in this folder
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim Fdate As Date
    Dim FileInFromFolder As Object


    FromPath = "U:\EXCEL\Report"  '<< Change
    ToPath = "U:\EXCEL\Report\toPathfolder"    '<< Change
    


Lastdayofmonth = DateSerial(Year(Date), Month(Date), 0)
firstdayofLastmonth = DateSerial(Year(Date), Month(Date) - 1, 1)


    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If


    If Right(ToPath, 1) <> "\" Then
        ToPath = ToPath & "\"
    End If


    Set FSO = CreateObject("scripting.filesystemobject")


    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If


    If FSO.FolderExists(ToPath) = False Then
        MsgBox ToPath & " doesn't exist"
        Exit Sub
    End If


    For Each FileInFromFolder In FSO.getfolder(FromPath).Files
        Fdate = Int(FileInFromFolder.DateLastModified)
        'Copy files from 1-Oct-2017 to 31-Oct-2017
        If Fdate >= DateSerial(2017, 10, 1) And Fdate <= DateSerial(2017, 10, 31) Then
            FileInFromFolder.Copy ToPath
        End If
    Next FileInFromFolder


    MsgBox "You can find the files from " & FromPath & " in " & ToPath


End Sub

 

[Fennek]

Hello,

a proposal, which MUST be debugged!

sub MoveFiles()
oPath = "u:\excel\report\"
nPath = "u:\excel\report\toPath\"

iFile = dir(oPath & "*.xls?")
iMonth = Month(now) - 1'check, maybe worsheetfunction.month
do while len(iFile)
if mid(ifile,7,2) = iMonth then
    CreateObject("wscript.shell").exec("cmd /c Move "oPath & iFile " & nPath "") 
end if
iFile = dir
loop
end sub

To my understanding in vba there is no "move", but copy and kill. In DOS (cmd.exe) there MOVE exists.

regards

 

[Fennek]

Hello,

the following code was tested:

Sub iFen()
oPath = "c:\temp\"
nPath = "c:\temp\Daten\"
iFile = "test1.txt"
CreateObject("wscript.shell").exec ("cmd /c Move " & oPath & iFile & " " & nPath & "")
End Sub

regards

 

[everblazing]

Hello,

the following code was tested:

Sub iFen()
oPath = "c:\temp\"
nPath = "c:\temp\Daten\"
iFile = "test1.txt"
CreateObject("wscript.shell").exec ("cmd /c Move " & oPath & iFile & " " & nPath & "")
End Sub

regards

Thank you very much for replying and your effort in testing. how would i include this in my code? which part would that sit under?

Regards
Lucas

 

[Fennek]

Hello,

please test the code carefully (I didn't)

sub MoveLastMonthFiles()
 FromPath = "U:\EXCEL\Report\"  '<< Change
    ToPath = "U:\EXCEL\Report\toPathfolder\"    '<< Change
iFile = dir(FromPath & *.*)
do while len(iFile)
    if Month(FileDateTime(FromPath & iFile)) = Month(now)-1 then
        CreateObject("wscript.shell").exec ("cmd /c Move " & FromPath & iFile & " " & ToPath & "")
    end if
iFile = dir
loop
end sub

regards

 

[everblazing]

Hello, thank you for your reply.

I have tested the code and its not moving any files. just opens a command window (Black window) and close.