Move Multiple Files to Various Folders (with varying criteria) 2

[RP1America]

Using Excel, I need to create a macro to be run daily (manually via command button) to move files all located in one source folder to various destination folders depending on the file name and whether or not the file's name contains a variation on today's date.

Source Folder:
"I:\RP\Source\"

Destination Folders:
"I:\RP\Destination\CashDetail"
"I:\RP\Destination\CheckRegister"
"I:\RP\Destination\Deposits"
"I:\RP\Destination\Excel Trade Sheets"

Sample Files:
CashDetail0803.xls
CashDetail0806.xls
CashDetail0807.xls
DEPOSITS0803.xlsx
DEPOSITS0806.xlsx
DEPOSITS0807.xlsx
Check_Register_Report_8 3 12.csv
Check_Register_Report_8 6 12.csv
Check_Register_Report_8 7 12.csv
TRAD080312.xlsx
TRAD080612.xlsx
TRAD080712.xlsx

Criteria:
Only files prior to today (in this case 8/7/12) should be moved.
Files beginning with "CashDetail" should be moved to the "CashDetail" destination folder.
Files beginning with "DEPOSITS" should be moved to the "Deposits" destination folder.
Files beginning with "Check_Register_Report_" should be moved to the "CheckRegister" destination folder.
Files beginning with "TRAD" should be moved to the "Excel Trade Sheets" destination folder.


Any help is greatly appreciated!

 

[SkipVought]

hi,

Check out the Name Statement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RP1America]

Thanks, Skip. I have checked it out and it looks to be what I am looking for. I have started trying to think through how to incorporate my criteria and keep coming up short.

Any thoughts on how to code for the given criteria?

 

[SkipVought]

What code do you have so far?

I'd suggest coding a function to parse the implied date in the filename string to return a real date. TWO of your filename strings, does not include year. You will need to address how to handle this, especially when it comes to the end-of-year/beggining-of-year boundary.

The folder issue can be easliy handled using the left several characters of the filename string.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

For the date of your file you may use:
[tt]
FileDateTime(PathToFile\FileName)
[/tt]
instead of relying on the date from your file name.

Have fun.

---- Andy

 

[MarkWalsh]

That assumes that the file date will match the date used in the name, which may or may not be the case. The reports may have been run on a different date than the date their data is related to. Also, the date of the file might change if it is sent through email, or other ways.

If the file's date can 100% reliably be said to be the correct date, then the FileDateTime would definitely be the better option.

 

[SkipVought]

THAT would not necessarily be a valid assumption, IMNSHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RP1America]

Thanks for all the tips, everyone. Unfortunately, this area of VBA is very new to me.

I believe using FileDateTime would be acceptable. However, I still am having trouble applying it or even just getting started on how to code this entire scenario.

I know this may be a tall order, but I wonder if anyone would be willing to show me the code they believe would work for this entire scenario. I am just not grasping how to accomplish this.

Thanks again!

 

[SkipVought]

So you have NO CODE? And you want someone else to do all the heavy lifting? NOTHING?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RP1America]

I don't even know where to begin. I am a novice. All I was able to do was to think of some of the variables needed. But as soon as I did that, I had no idea where to go with it.

'Name OldPathName As NewPathName

Dim strMMDD As String, strMDYY As String, strMMDDYY As String
strMMDD = Format(Date, "mmdd")
strMDYY = Format(Date, "m d yy")
strMMDDYY = Format(Date, "mmddyy")

Dim strCDdate As String, strCRdate As String, strTdate As String
'?

Dim SourceCashDetail, SourceDeposits, SourceCheckRegister, SourceTRAD, _
DestCashDetail, DestDeposits, DestCheckRegister, DestTRAD

SourceCashDetail = "I:\RP\Source\CashDetail" & strCDdate & ".xlsx"

Format(FileDateTime("I:\RP\Source\CashDetail0802.xls"), "mmdd") 'returns 0802

 

[SkipVought]

Paste in a module and run Main. You will need to fill in the remainder of the Select Case statements for the remaining destinations.

I would COMMENT out the Name x As y statement until I was absolutely sure that the X and Y were EXACTLY what I expect. Use a Debug.Print statement to verify these results.

Sub Main()
    Dim oFSO As Object, oFile As Object, sFROM As String
'I:\RP\Source    sFROM = "I:\RP\Source"
'I:\RP\Destination\CashDetail
'I:\RP\Destination\CheckRegister
'I:\RP\Destination\Deposits
'I:\RP\Destination\Excel Trade Sheets

    With CreateObject("Scripting.FileSystemObject")
        For Each oFile In .GetFolder(sFROM).Files
            
            If FileDate(oFile.Name) < Date Then
                Select Case LCase(Left(oFile.Name, 2))
                    Case "ca"
'[highlight]this is an example[/highlight]
                        Name oFile.Path As "I:\RP\Destination\CashDetail\" & oFile.Name
                    Case "ch"
                
                    Case "de"
                
                    Case "tr"
                
                End Select
            End If
        Next
    End With
End Sub

Function FileDate(FNAME As String)
    Dim sDAT As String, aDAT, i As Integer
    
    sDAT = Split(FNAME, ".")(0)
    sDAT = Split(sDAT, "_")(UBound(Split(sDAT, "_")))
    sDAT = RemAlpha(sDAT)
    
    aDAT = Split(sDAT, " ")
    
    For i = 0 To UBound(aDAT)
        If UBound(aDAT) = 0 Then
            If Len(sDAT) = 4 Then
                FileDate = DateSerial(Year(Date), Mid(sDAT, 1, 2), Mid(sDAT, 3, 2))
            Else
                FileDate = DateSerial("20" & Mid(sDAT, 5, 2), Mid(sDAT, 1, 2), Mid(sDAT, 3, 2))
            End If
        Else
            FileDate = DateSerial("20" & aDAT(2), aDAT(0), aDAT(1))
        End If
    Next
    
    
End Function

 
Function RemAlpha(strS As String)
':remove ALPHA from a string
     Dim re As Object ' object to hold Regular Expression object
    
     Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application
    
     With re
         .Global = True ' find all matches not just first
         .MultiLine = True ' over multiple lines
         .IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
         .Pattern = "[A-Z]" ' regular expression for numeric range
         RemAlpha = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
     End With
  
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I was playing with your problem yesterday, but – like Skip – was also waiting to show some code / effort by you

This is just to get you going. This is by no means ready code, just the idea. If the date of the file is not what you want to use, use Skip’s function FileDate to get the date from file’s name.

Option Explicit
Private Const SOURCE_FOLDER  As String = "I:\RP\Source\"
Private Const DEST_MAIN As String = "I:\RP\Destination\"

Sub Macro1()

Dim strFile As String
Dim strDest As String

strFile = Dir(SOURCE_FOLDER)

Do While strFile <> ""
    If CDate(Split(FileDateTime(SOURCE_FOLDER & strFile), "")(0)) < Date Then
        strDest = ""
        Select Case True
            Case InStr(strFile, "CashDetail")
                strDest = "CashDetail\"
            Case InStr(strFile, "DEPOSITS")
                strDest = "Deposits\"
            Case InStr(strFile, "Check_Register_Report_")
                strDest = "CheckRegister\"
            Case InStr(strFile, "TRAD")
                strDest = "Excel Trade Sheets\"
            Case Else
                MsgBox strFile & " File Not Recognized."
        End Select
        If strDest <> "" Then
            MsgBox "Move file: " & SOURCE_FOLDER & strFile & _
                " to " & DEST_MAIN & strDest & strFile
        End If
    End If
    
    strFile = Dir
Loop

End Sub

Have fun.

---- Andy

 

[RP1America]

Thank both of you so much for your input. Having not done anything like this in the past, I was lost on where to begin. Now that I have something to work from, I will be able to understand what is going on and how to apply in the future. I will work out the code from your examples and post any additional questions and/or the result.

Again, thank you!!

 

[SkipVought]

When you have a question like this, it is best to show what you have done and explaine what you have tried, along with a description of what happened when you tried to make it work and maybe failed.

There are plenty of members who will gladly share their expertise to help a struggling member. But show your efforts, however strong or feeble.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]