How to determine if a file exists

[TotallyConfused]

' I need some way to determine whether or not a given file exists. The past couple of days have been spent searching the internet and
' trying different pieces of code I've found. None of them work for me.The second workbook, "MasterDataFile.xlsm" was created by me
' and works okay. The problem is that this program will be used by another person, so I'd like to have ThisWorkbook create the file the
' first time the user runs this program rather than have them create this data file.This should avoid the possibility of them making a
' spelling error or placing the data file in the wrong place, etc. This file will only be created once, then used over and over, so that's why
' I need that 'IF' statement.
'
Sub DoesFileExist()
    Dim fPath As String                                               ' Path where data and programs are at
    Dim fName As String                                            ' File name of Master data file
fName = "MasterDataFile.xlsm"                   ' Assign name of MasterDataFile, which stores ALL of the data, to a variable
fPath = Application.ActiveWorkbook.Path & "\"       ' Determine path for the MasterDatafile.xlsm based on Activeworkbook path
fName = fPath & fName                                           ' Determine full path and file name of MasterDataFile.xlsm
' Workbooks.Open fName
'******* Above code using Workbooks.Open fName works because MasterDataFile.xlsm DOES exist, so the file does open *******

' The following code was posted to MrExcel by someone named "DK" on April 12, 2004. The link to this is:
'    https://www.mrexcel.com/board/threads/how-to-check-if-file-exists.61179/page-2#posts
' The only change I've made is to comment out the original line of 'If oFSO.fileexists...." and insert what I hoped would work for me.
' I'm unable to code in the exact path because this program will be used by another person, so I'm using a variable instead.
' I've tried different versions, including putting everything inside parenthesis, using fName by itself, etc. I either get an immediate
' syntax error message or MsgBox says it doesn't exist.

'Sub LateBinding()
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FilesystemObject")
    If oFSO.fileexists(fName) = True Then                   ' If oFSO.fileexists(fPath)  & "MasterDataFile.xlsm" = True Then
'   If oFSO.fileexists("H:\temp\myfile.xls") = True Then
        MsgBox "It exists!"
    Else
        MsgBox "Sorry mate, it doesn't exist."
    End If
' End Sub
End Sub

' I have no emotional attachment to the above code, in fact, I have no idea what the line of 'Set oFSO...' does. I found this macro
' and hoped I could adapt it to my use. If you can tell me what I'm doing wrong here or you have some other code that will tell me
' whether or not a file exists, I'd sure appreciate it. I'd love to receive any help or suggestions you may be able to offer.
' THANK YOU in advance.

TotallyConfused

 

[VBasic2008]

Try these:

Option Explicit

Sub DoesFileExist()
    
    Const FileName As String = "MasterDataFile.xlsm"
    
    Dim FolderPath As String
    FolderPath = ThisWorkbook.Path
    Dim FilePath As String
    FilePath = FolderPath & Application.PathSeparator & FileName
    
    If Not FileExists(FilePath) Then
        Application.ScreenUpdating = False
        With Workbooks.Add
            ' You might wanna improve here e.g. add title, headers ...
            .SaveAs FileName:=FilePath, _
                    FileFormat:=xlOpenXMLWorkbookMacroEnabled
            .Close SaveChanges:=False
        End With
        Application.ScreenUpdating = True
        MsgBox "Master workbook created!", vbInformation
    Else
        MsgBox "Master workbook already exists!", vbExclamation
    End If

End Sub

Function FileExists(ByVal FilePath As String) _
         As Boolean
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(FilePath) Then
            FileExists = True
        End If
    End With
End Function

 

[TotallyConfused]

Hello VBasic2008

I want to THANK YOU for your reply to my question. I pasted your code into a new WorkBook and when I ran your code for the FIRST time, it created a new data file just as I hoped. I opened it and put a number in that new blank data file, saved and closed it. This is what the user will be doing. Now I tried running your code again, which is what the user will be doing the following day. This is where a problem occurred. Your code DID NOT find that data file, so it created another one, which of course destroyed the data I'd placed in it.

I admit, I'm only slightly familiar with functions, but it seems as if your function is where the problem lies because it is NOT detecting that data file. I'm only guessing at that so I may be wrong.

THANK YOU for your effort so far.

TotallyConfused

 

[VBasic2008]

Hello VBasic2008

I want to THANK YOU for your reply to my question. I pasted your code into a new WorkBook and when I ran your code for the FIRST time, it created a new data file just as I hoped. I opened it and put a number in that new blank data file, saved and closed it. This is what the user will be doing. Now I tried running your code again, which is what the user will be doing the following day. This is where a problem occurred. Your code DID NOT find that data file, so it created another one, which of course destroyed the data I'd placed in it.

I admit, I'm only slightly familiar with functions, but it seems as if your function is where the problem lies because it is NOT detecting that data file. I'm only guessing at that so I may be wrong.

THANK YOU for your effort so far.

TotallyConfused

If that were true, you would have mentioned that Excel 'had asked you' if you would like to overwrite the file. Since you didn't, something else is happening. When you create a new workbook it has no path i.e. its path is "". So I don't know where it would create the file, but I'm guessing in the so called Current Directory or Current Drive. In my case it created it on my F drive. Anyway, I would guess that you now have two of those files, one with the number and a new one which you think was the first one. When you will be giving the file to the user, he will be saving it somewhere. So you do the same, save the file and then test the code. You can easily test the function for existence of this or any other file, e.g.:

Sub testFunction
    Const FilePath As String = "C:\Test\MasterDataFile.xlsm"
    Debug.Print FileExists(FilePath)
End Sub

It just returns True or False.

 

[TotallyConfused]

Hello VBasic2008

' I just finished testing your code once again, only this time in a little more detail. Here is what I did.
' 1 ) From my harddrive I deleted any file named 'MasterDataFile.xlsm'
' 2 ) I ran your program. MsgBox said 'Master workbook created' which is what it should say.
' 3 ) To see what was there, I loaded Windows File Explorer (or whatever it's called now) and checked the contents of the folder referred to
' by the variable of 'FolderPath'. 'MasterDataFile.xlsm' was there.
' 4 ) Using File Explorer, I opened 'MasterDataFile.xlsm' just to be sure it was okay. It was a blank workbook as it should be.
' 5 ) I entered a number, then saved and closed the workbook.
' 6 ) To double check to make sure I'd done everything properly, I once again opened 'MasterDataFile.xlsm' and there was the number
' I'd entered. Then I saved and closed the workbook.
' 7 ) I then ran your program once again. As happened before, (see step #2) MsgBox said 'Master workbook created'.
' 8 ) Again using File Explorer, I opened 'MasterDataFile.xlsm' to find myself looking at a BLANK sheet. The number I'd entered was gone.

' You were wondering whether 'MasterDataFile.xlsm' had been created in another folder after the first time the program was run. How
' could this happen, since the same path was being used each time? Every time I checked the contents of the folder, not only was
' 'MasterDataFile.xlsm' there, but also ThisWorkbook where the code is being run from. This is the way it should be.

' I forgot to mention it the other day, but at no time when I've ran your code has MsgBox ever displayed, 'Master workbook already exists'.
' The only message I've seen was the one mentioned in steps #2 & 7.
' I wish I knew more about VBA so I could offer some hint for a solution of the problem. I do want to THANK YOU for your time and effort
' you've devoted to trying to help me. It hasn't been a total loss, as I've seen a few commands I'd never encountered before. Learn
' something new every day.



' I think I've worked out a solution to my problem of determining whether a file exists. It occurred to me that the command 'Open' would
' fail if the named workbook was not there. Using this idea and also the 'On Error Goto...' I've come up with the following code which
' seems to work okay.

Sub DoesFileExist()                                                           ' Open MasterDataFile & if it doesn't exist, then create it.
    On Error GoTo FileDoesNotExist:                                   ' What to do if MasterDataFile.xlsm can't be opened because it doesn't exist
    Dim fPath As String                                                       ' Path were data and programs are at
    Dim fName As String                                                     ' File name of Master data file
    Dim Wb2 As Workbook                                                 ' Second workbook to be used called 'MasterDataFile.xlsm'
  Application.DisplayAlerts = False                                     ' Suppress user interface box telling user file already exists or can't be found
  fName = "MasterDataFile.xlsm"                                        ' Assign name of MasterDataFile, which stores ALL of the data, to a variable
  fPath = Application.ActiveWorkbook.Path                         ' Determine path for the MasterDatafile.xlsm based on Activeworkbook path
  Workbooks.Open fPath & "\" & fName, UpdateLinks:=0    ' Open MasterDataFile.xlsm
  Application.DisplayAlerts = True                                      ' Turn on user interface box telling user file already exists or can't be found
Exit Sub

FileDoesNotExist:
     Set Wb2 = Workbooks.Add                                                           ' MasterDataFile does not exist so create it now
     ActiveWorkbook.SaveAs fPath & "\" & fName, FileFormat:=52    ' FileFormat of '52' means file saved as MacroEnabled with the '.xlsm' extension
End Sub

Again THANKS for your help.

TotallyConfused