Conditional statement before Renaming

[thardin]

This is a perfectly good Macro I created to move 5 files on a daily basis.

However, I just want to add a conditional statement before each rename to check if the file exists. If yes, do the rename. and if not, MSGBOX "File doesn't exist"
How do you recommend doing this?
Any other feedback is appreciated.
Thanks

Sub MoveFiles()
'
' MoveFile Macro
' Keyboard Shortcut: Ctrl Shift + L

'---------------------------------------------------------------------------
'Daily:
'-Move today's B&E, Del To Fund, Bene Default, and Firm C Share files
'-Move yesterday's Aggin file
'On Mondays:
'Move Saturdays "Del to Fund" File
'Move Friday's Aggin File
'----------------------------------------------------------------------------

'
Dim strYearLong As String
Dim strMonthShort As String
Dim strMonthLong As String
Dim strDay As String
Dim strFullDate As String
Dim strMonthFolder As String
Dim strDrive As String
Dim Acut As String, Bcut As String, Dcut As String, Ecut As String, Fcut As String
Dim APaste As String, BPaste As String, DPaste As String, EPaste As String, FPaste As String
Dim APath As String, BPath As String, DPath As String, EPath As String, FPath As String
Dim AgginFileTitle As String, BE_FileTitle As String, Del_to_Fund_FileTitle As String, Bene_Default_FileTitle As String, Firm_Name_FileTitle As String, FileExtension As String
Dim TodayDate As String, AgginDate As String
Dim CutfromPath As String, PastetoPath As String
Dim AgginCutfromPath As String
Dim DeltoFundDate As String
Dim DeltoFundFriDate As String



strYearLong = Format(Now, "yyyy")
strMonthShort = Format(Now, "mm")
strMonthLong = Format(Now, "mmmm")
strDay = Format(d, "DD")
TodayDate = Format(Date, "YYYYMMDD")
AgginDate = Format(Date - 1, "YYYYMMDD")
strMonthFolder = strMonthShort & "-" & strMonthLong
AgginFriDate = Format(Date - 3, "YYYMMDD")
DeltoFundDate = Format(Date, "YYYYMMDD")

If Weekday(Date) = vbMonday Then
AgginDate = Format(Date - 3, "YYYYMMDD")
DeltoFundDate = Format(Date - 2, "YYYYMMDD")

Else
AgginDate = Format(Date - 1, "YYYYMMDD")
DeltoFundDate = Format(Date, "YYYYMMDD")


End If

'current location of file
strDrive = "X:"
CutfromPath = strDrive & "\operations\euc\Dept_Reports\MF\529_Exceptions\"
AgginCutfromPath = strDrive & "\surpas_files_tempe\EPM\output\"

'location to paste to
PastetoPath = strDrive & "\shareholder_accounting\"



'Final File destination based on file & current date
APath = PastetoPath & "529 BASIS + EARNINGS\REPORTS\CROSSOVER\EPM\" & strYearLong & "\"
BPath = PastetoPath & "MF Trade Review\American 529 Daily Reports\Basis & Earnings Rollovers\" & strYearLong & "\" & strMonthFolder & "\"
DPath = PastetoPath & "529 C Share Restriction Controls\Fund Held\Delivered to Fund\EPM_output\" & strYearLong & "\" & strMonthFolder & "\"
EPath = PastetoPath & "529 Bene Default\" & strYearLong & "\" & strMonthFolder & "\"
FPath = PastetoPath & "529 C Share Restriction Controls\Firm Name\Daily EPM Reports\" & strYearLong & "\" & strMonthFolder & "\"

' If Month subdirectory doesn't exist, create one
If Len(Dir(BPath, vbDirectory)) = 0 Then
MkDir BPath
End If

If Len(Dir(DPath, vbDirectory)) = 0 Then
MkDir DPath
End If

If Len(Dir(EPath, vbDirectory)) = 0 Then
MkDir EPath
End If

If Len(Dir(FPath, vbDirectory)) = 0 Then
MkDir FPath
End If


'File Name part 1
AgginFileTitle = "AGGIN-Crossover__Accounts_DAILY-"
BE_FileTitle = "OBSB158_B_&_E_Rollover_Query-10_digit_"
Del_to_Fund_FileTitle = "OBSB158_Delivered_to_Fund_"
Bene_Default_FileTitle = "OBSB158_Bene_Default_"
Firm_Name_FileTitle = "OBSB158_529_C_Shares_Age_LE_12_Transactions-_"
FileExtension = ".xlsx"

'File Names & Current Date & Extension
AgginFile_Name = AgginFileTitle & AgginDate & FileExtension
BE_File_Name = BE_FileTitle & TodayDate & FileExtension
Del_to_Fund_File_Name = Del_to_Fund_FileTitle & DeltoFundDate & FileExtension
Bene_Default_File_Name = Bene_Default_FileTitle & TodayDate & FileExtension
Firm_Name_File_Name = Firm_Name_FileTitle & TodayDate & FileExtension

'Variables for successful Cut
Acut = AgginCutfromPath & AgginFile_Name
Bcut = CutfromPath & BE_File_Name
Dcut = CutfromPath & Del_to_Fund_File_Name
Ecut = CutfromPath & Bene_Default_File_Name
Fcut = CutfromPath & Firm_Name_File_Name

'Variables for successful move
APaste = APath & AgginFile_Name
BPaste = BPath & BE_File_Name
DPaste = DPath & Del_to_Fund_File_Name
EPaste = EPath & Bene_Default_File_Name
FPaste = FPath & Firm_Name_File_Name



'Aggin Move -good
FileCopy Acut, APaste

With ActiveSheet
.Range("A4").Select
End With


'Del to Fund Move-good
Name Dcut As DPaste

With ActiveSheet
.Range("A4").Select
End With

'Firm Name C Share Move -good
Name Fcut As FPaste


With ActiveSheet
.Range("A1").Select
End With

'Bene Default move- good
Name Ecut As EPaste

With ActiveSheet
.Range("A2").Select
End With

'B&E Move - good
Name Bcut As BPaste

If Weekday(Date) = vbWednesday Then
Workbooks.Open APaste

End If

End Sub

Sub WedAggin()

'Keyboard Shortcut: Ctrl+Shift+E
Dim strYearLong As String, strMonthShort As String, strMonthMed As String, strYearShort As String, strFullDate As String
Dim strTabDay As String
Dim FilePath As String, MonthFile As String, OpenFile As String
Dim AgginFile As String, LinuxPath As String, LinuxFile As String
Dim intDay As Integer
Dim strAgginFileDate As String

strYearLong = Format(Now, "yyyy") - 1
strMonthShort = Format(Now, "mm")
strMonthMed = Format(Now, "mmm")
strYearShort = Format(Now, "yy")
strTabDay = Format(Date - 1, "d") & "th"
strFullDate = Format(Date, "yyyymmdd")
strAgginFileDate = Format(Date - 1, "yyyymmdd")

If Day(Date - 1) = 1 Then
strTabDay = "1st"
ElseIf Day(Date - 1) = 2 Then
strTabDay = "2nd"
ElseIf Day(Date - 1) = 3 Then
strTabDay = "3rd"
ElseIf Day(Date - 1) = 21 Then
strTabDay = "21st"
ElseIf Day(Date - 1) = 22 Then
strTabDay = "22nd"
ElseIf Day(Date - 1) = 23 Then
strTabDay = "23rd"
ElseIf Day(Date - 1) = 31 Then
strTabDay = "31st"
Else: strTabDay = Format(Date - 1, "d") & "th"

End If



'Open Month File
FilePath = "X:\shareholder_accounting\529 BASIS + EARNINGS\REPORTS\CROSSOVER\DAILY REPORT\" & strYearLong & "\"
MonthFile = strMonthShort & "-" & strMonthMed & "-" & strYearShort
OpenFile = FilePath & MonthFile & ".xlsx"
AgginFile = "AGGIN-Crossover__Accounts_DAILY-" & strAgginFileDate
LinuxPath = "X:\shareholder_accounting\529 BASIS + EARNINGS\REPORTS\CROSSOVER\"
LinuxFile = "Linux Co-SuRPAS Hot Key Macro vL1"

Workbooks.Open FilePath & MonthFile & ".xlsx"

With ActiveWorkbook
Worksheets(strTabDay).Activate
End With

'copy and paste data from yesterday's aggin file to Current month file

Workbooks(AgginFile).Activate

Sheets("Sheet").Cells.Select
Selection.Copy


Workbooks(MonthFile).Activate
Worksheets(strTabDay).Activate
ActiveSheet.paste



'sort by Column C from oldest to newest (in current month file)
With ActiveSheet
.Range("A1").CurrentRegion.Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes
'copy column B accounts
Range("B2", Range("B2").End(xlDown)).Select
Selection.Copy
End With


'Open Linux file & paste in cell A17
Workbooks.Open LinuxPath & LinuxFile & ".xlsm"
ActiveSheet.Range("A17").PasteSpecial xlPasteValues


End Sub

 

[rlv01]

I just want to add a conditional statement before each rename to check if the file exists. If yes, do the rename. and if not, MSGBOX "File doesn't exist"
How do you recommend doing this?
Any other feedback is appreciated.
Thanks

If I were doing it, I'd a subroutine to encapsulate the rename and file check. Not tested.

    'B&E Move - good
    'Name Bcut As BPaste
    RenameFile Bcut, BPaste

'Rename CutFileName to PasteFileName
Sub RenameFile(CutFileName As String, PasteFileName As String)
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(CutFileName) Then
            Name CutFileName As PasteFileName
        Else
            MsgBox "Invalid File Path - " & CutFileName
        End If
    End With
End Sub

Question: Is there a reason you did not use code tags to post your code? Usually when someone posts 250 lines of code and does not use code tags, I will abandon the thread and move on unless the poster is a newbie who does not know better. But you have 90+ posts here and have used code tags in some of your other threads, so presumably you know how.