Check for existing File name, open if exist or open new workbook if not

Hi all,

I need help again with my code. I have code but I can't get it to work, it just stops and I am hoping that another set of eyes will see what I missed. SIPPath is my variable for building the file path and Swb is my variable for the full file name. Both are defined as strings and ext is variable as string for file extension ".xlsm".

I use this code to build the daily file path (SIPPath), if it does not exist to the file name, then build the daily filename (Swb) + extension (ext) as we need for daily file system. I need it to check for an existing file name and if it exists then open the file, if the file name does not exist then open the file with that days file name.

All help is welcomed and appreciated.

Private Sub Workbook_Open()

                
         If Target.Cells.Count > 1 Then Exit Sub
            If Target.Value = "" Then Exit Sub
    
            On Error GoTo EndItAll:
            Application.EnableEvents = False
            
            'Working Code For Sign-In-Workbook Master 10 06 2017
            
            'Check for Year & Month folders Exists, if not Then create
            Dim SIPPath As String
            Dim Swb As String   'Swb = Daily Sign In Workbook File Name
            Dim ext As String  'ext = File name Extension for Excel Macro Enabled File
            ext = ".xlsm"
            
            'CHANGE DRIVE LETTER "E" TO "S" TO RUN ON DPS IMPOUND COMPUTERS
            SIPPath = "C:\Phoenix XXShared\XX\Sign In Logs\" & Format(Date, "yyyy") & "\"
    
                If Dir(SIPPath, vbDirectory) = "" Then MkDir SIPPath
    
                    SIPPath = SIPPath & Format(Date, "MM") & " " & Format(Date, "YYYY") & "\"
    
                        If Dir(SIPPath, vbDirectory) = "" Then MkDir SIPPath

                        Swb = SIPPath & Format(Now, "MM") & "-" & Format(Now, "DD") & "-" & Format(Now, "YYYY") & " Sign-In-Workbook" & ext
 
                        If Dir(Swb, vbDirectory) <> "" Then Workbooks.Open (Swb)

                       Else
                        
                        'Saves File with Daily Log Name if not found
                        ActiveWorkbook.SaveAs Filename:=SIPPath & Format(Now, "MM") & "-" & Format(Now, "DD") _
                        & "-" & Format(Now, "YYYY") & " Sign-In-Workbook", FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                        CreateBackup:=False
        
                    ActiveWorkbook.Save
        
                If Worksheets("SIGN-IN").Range("B1").Value <= 0 Then Worksheets("SIGN-IN").Range("B1").Value = Date
                If Worksheets("CREDIT_CARDS").Range("B4").Value <= 0 Then Worksheets("CREDIT_CARDS").Range("B4").Value = Date
                If Worksheets("HP_DEPOSIT").Range("B12").Value <= 0 Then Worksheets("HP_DEPOSIT").Range("B12").Value = Date
                If Worksheets("CAP_XX_DEPOSIT").Range("B12").Value <= 0 Then Worksheets("CAP_XX_DEPOSIT").Range("B12").Value = Date
              
              End If
              
                
            'This will Auto Save Active Workbook every 5 minutes
            Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
        
End Sub
           
EndItAll:
    Application.EnableEvents = True
End Sub

Well we use this at work and several of us will have to use the log at different times during the day. The file is opened on each users computer. We use a master form for each day and Two of our users are NOT computer users at all. Several time they have had two workbooks open at the same time. I have a routine that will save the work book every 5 minutes and whenever closed. It has happened that they close the workbooks without saving and some how have closed workbooks with corrupted data saving some of the prior days information in the workbook dated for today. I have been trying to bullet proof the files by adding the auto save and save on close routines. I was trying to have the Master workbook check for the days file existing and if it is in the directory to automatically open it with no user input, if it doesn't exist then create it, as I think they are messing it up by answering the "file exists do you want to overwrite it?" warning. As I said I was trying to bullet proof the file because I know I can't fix the users. Any suggestions are very welcome.

Thanks for the quick response.
I did not realize that I was using a directory check instead of a filename test. I am very new to VBA. Thanks again for the help.

To clarify the SIPPath is just the path builder, after I have the path I built the File name that includes the SIPPath in the Swb Filename (Swb = SIPPath & Swb & ext). So Swb is the complete path with the file name (Swb = C:\Path\MM-DD-YYYY Sign In Log.xlsm).

Thanks again for your help


I put your code in and now I get a "Else without an If" error. I don't know why. Do you have any suggestion?

Thanks for the help but, now it runs to the Else statement and stops with error "Else without If". I don't understand why it stops when there is an If two lines back. Any more ideas?