- 1). Copy the following code:
Option Explicit
Function 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 evaluation
On Error Resume Next
iTemp = GetAttr(PathName)
'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
'Resume error checking
On Error Goto 0
End Function
Sub 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 here
sPath = "C:\Test.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
End If
End Sub
Sub 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 here
sPath = "HardDriveName:Documents:Test.doc"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
End If
End 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.
Source...