Multiple Apps

Check if directory or file exists (using VBA, not FSO)

Ease of Use

Intermediate

Version tested with

2000, 2002, 2003, 2004 (Mac) 

Submitted by:

Ken Puls

Description:

This function can be used to check if a specific folder or file exists. It works on both Windows and Macintosh systems. 

Discussion:

It is a best practice to test if a folder exists before you try to write a file to it, and to test if a specific file exists before you try to open it in your code. Getting the error in the middle of your procedure is never a good thing, as it is distracting to your users and can result in problems. This procedure allows you to test for the existence of critical files/directories and take appropriate actions based on the results returned. Since adding this to the Knowledge Base, xCav8r has submitted two similar entries that use the File System Object to return the same results. It should be noted, however, that the File System Object will not work with the Macintosh. If you are interested in the alternate methods, you will need to get both entries, since they make a distinction between folders and files. The first called "Determine if a folder exists using the File System Object (FS0)." (http://www.vbaexpress.com/kb/getarticle.php?kb_id=634) The second is entitled "Determine if a file exists using the File System Object (FS0)." (http://www.vbaexpress.com/kb/getarticle.php?kb_id=635) 

Code:

instructions for use

			

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

How to use:

  1. Copy above code.
  2. In the Office Application press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Open the VBE again.
  2. Change the path to your file in the appropriate Sub TestItWith procedure. (sPath = ???)
  3. Press F5 to run the procedure.
  4. You should see a message telling you that the file/directory does or does not exist.
  5. An Excel based sample file is attached which demostrates how to use the formula from a worksheet cell or in VBA.
 

Sample File:

FileOrDirExists.zip 7KB 

Approved by xCav8r


This entry has been viewed 316 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express