How to Determine if a File Exists in Excel VBA

1. Copy the following code:Option ExplicitFunction FileOrDirExists(PathName As String) As Boolean'Macro Purpose: Function returns TRUE if the specified file' or folder exists, false if not.'PathName : Supports Windows mapped drives or UNC' : Supports Macintosh paths'File usage : Provide full file path and extension'Folder usage : Provide full folder path' Accepts with/without trailing '\' (Windows)' Accepts with/without trailing ':' (Macintosh)Dim iTemp As Integer'Ignore errors to allow for error evaluationOn Error Resume NextiTemp = GetAttr(PathName)'Check if error exists and set response appropriatelySelect Case Err.NumberCase Is = 0FileOrDirExists = TrueCase ElseFileOrDirExists = FalseEnd Select'Resume error checkingOn Error Goto 0End FunctionSub TestItWithWindows()'Macro Purpose: To test the FileOrDirExists function with Windows'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'C:\Test.xls''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd SubSub TestItWithMacintosh()'Macro Purpose: To test the FileOrDirExists function with a Macintosh'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'HardDriveName:Documents:Test.doc''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd Sub
2. Open Excel and press 'Alt-F11' to enter the Visual Basic Editor.
3. Click 'Insert' and then click 'Module.'
4. Paste the code into the right-hand pane by pressing 'Ctrl-'V.'
5. Change 'text.xls' to the file name you are searching for.
6. Press 'F5' to run the procedure. The procedure will return a pop-up window telling you whether the file exists.