If Then Function Needed to Skip Code if File Exists in Certain Location

[lora.johnnson]

I have several template files that I plan to add the following code to:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fname As String
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)

Sheet1.Protect "123"
Sheet2.Protect "123"
Sheet3.Protect "123"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:="C:\Line 1 Data\" & MonthName(MyMonth) & Day(Date) & Year(Date) & ThisWorkbook.Name
Application.DisplayAlerts = True
End Sub

Problem is that anytime the Data file is opened and then closed it creates another file that is named "November 3 2010 November 3 2010 Data Name" because it is rerunning the above code. Can the above code be modified to add an If Then Else function that says If the File is already in folder "C;\Line 1 Data\" Then ThisWorkbook.Save Else ThisWorkbook.SaveAs Filename:="C:\Line 1 Data\" & MonthName(MyMonth) & Day(Date) & Year(Date) & ThisWorkbook.Name EndIf. I've tried saying just that and it doesn't do anything. I am a beginner so copying code is about the best I can do.

 

[alan.m]

Can the above code be modified to add an If Then Else function that says
If the File is already in folder "C;\Line 1 Data\" Then
ThisWorkbook.Save Else ThisWorkbook.SaveAs Filename:="C:\Line 1 Data\" & MonthName(MyMonth) & Day(Date) & Year(Date) & ThisWorkbook.Name EndIf. I've tried saying just that and it doesn't do anything. I am a beginner so copying code is about the best I can do.

Hi Lora,
try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyMonth As Integer
Dim myDir As String

MyMonth = Month(Date)
myDir = CurDir()

    If myDir = "C:\Line 1 Data" Then
        ThisWorkbook.Save
    Else
        Sheet1.Protect "123"
        Sheet2.Protect "123"
        Sheet3.Protect "123"
        Application.DisplayAlerts = False
            ThisWorkbook.SaveAs Filename:="C:\Line 1 Data\" & MonthName(MyMonth) & Day(Date) & Year(Date) & ThisWorkbook.Name
        Application.DisplayAlerts = True
    End If
End Sub

I also took out a couple of Dim statements that didn't get used.
Hope that this helps,
Cheers,
Alan

 

[lora.johnnson]

Still same problem I'm still getting a duplicate data file anytime I open the saved data file and make any changes. Perhaps I'm going at this all wrong, here’s what I want to do in a nut shell. I have a file in location X that is a template. I want to open it and enter data and then close it, but at close I want it to automatically protect all sheets and save as "Date Filename" in Line 1 Data folder. Then the following day a supervisor would open the "Date Filename" in the Line 1 Data folder and authorize it. This time at close the file should just save so that we don't have two of the same file "essentially" with two different names.

 

[alan.m]

Sorry Lora,
I used
myDir = CurDir()
I should have used
myDir = ActiveWorkbook.Path
Change that one line and I think it will work - it's working for me.

 

[lora.johnnson]

SWEETNESS!!!!! Your the man alan.m!