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.

Please Login or Register  to view this content.

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?