Multiple Apps

Determine if a folder exists using the File System Object (FSO)

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

xCav8r

Description:

When given a Windows path to a folder, this function returns true if it exists; false, if it doesn't. 

Discussion:

An easy way to avoid errors is to check if a folder exists before trying to save or move a file there. This function uses the File System Object to determine if the folder exists or not. This is an alternative to kplus' KB entry entitled "Check if directory or file exists." His function arrives at the same result, but without the File System Object. His also supports macintosh paths. (http://www.vbaexpress.com/kb/getarticle.php?kb_id=559) This is related to another KB entry entitled "Test if a file exists using the File System Object". (http://www.vbaexpress.com/kb/getarticle.php?kb_id=635) NB: This will not work on the Macintosh. 

Code:

instructions for use

			

'--------------------------------------------------------------------------- ' NB: I have avoided abbreviating variable and procedure names to make ' things easier to grasp for beginners and non-native English speakers. ' Feel free to use Find and Replace to swap these names with something ' you prefer. '--------------------------------------------------------------------------- '--------------------------------------------------------------------------- ' One of the requirements for VBAX Knowledge Base submissions is that Option ' Explicit be used. As a rule of thumb, I always use Option Explicit to reduce ' mistakes, but some of the code in my testing sub procedure requires that it ' be turned off. See TestMyFolderExists below for more details. ' Option Explicit '--------------------------------------------------------------------------- '--------------------------------------------------------------------------- ' Insert paths into the quotations below. These will be used to test the ' MyFolderExists function. The path may be a mapped drive path or a UNC Path. ' See the comments for the MyFolderExists function for more information. ' ' EG.: "D:\MyFolder\" (mapped drive path) ' EG.: "\\MachineName\VolumeName\MyFolder\" (UNC) Private Const mcstrValidPathToFolder As String = "C:\temp" Private Const mcstrInvalidPathToFolder As String = "C:\temp2" '--------------------------------------------------------------------------- Private Sub TestMyFolderExists() '--------------------------------------------------------------------------- ' Desc : This sub procedure calls the MyFolderExists function using the ' module level constants defined above as the arguments, ' printing the results to the immediate window. You need to ' adjust the values of these strings for your own file system. ' ' Remarks : The purpose of this procedure is to demonstrate how the ' MyFolderExists function *might* be used. It is included ' here soley for that reason, and it is not required for the ' function to work. '--------------------------------------------------------------------------- ' NB: These constants can be replaced with any valid string enclosed by ' quotation marks Debug.Print MyFolderExists(mcstrValidPathToFolder) Debug.Print MyFolderExists(mcstrInvalidPathToFolder) ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Application Specific Examples Below: ' ' To use these examples, uncomment the block below and comment out Option ' Explicit. See comments at beginning of module for more details. ' ' NB: You must save your document, workbook, etc., if you want to use these ' application specific examples. If your project isn't saved, then by ' default the path will be an empty string. ' - - - - - - - - - - - - - - - - - - - - - - - - - ' Select Case Application.Name ' Case "Microsoft Access" ' Debug.Print MyFolderExists(CurrentProject.Path) ' Case "Microsoft Excel" ' Debug.Print MyFolderExists(ThisWorkbook.Path) ' Case "Microsoft PowerPoint" ' Debug.Print MyFolderExists(ActivePresentation.Path) ' Case "Microsoft Publisher", "Microsoft Word", "Microsoft Visio" ' Debug.Print MyFolderExists(ActiveDocument.Path) ' Case "Microsoft Project" ' Debug.Print MyFolderExists(ActiveProject.Path) ' Case Else ' 'Application not planned for; skip ' End Select ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - End Sub Public Function MyFolderExists(Path As String) As Boolean '--------------------------------------------------------------------------- ' Desc : When given a path to a folder, this function returns TRUE if ' the folder exists and FALSE if it doesn't. ' ' Argument : The File System Object supports both mapped drive and UNC ' paths. ' ' The trailing slash does not matter. "C:\temp" and ' "C:\temp\" are equally valid: both return TRUE. ' ' Required : Microsoft Scripting Runtime (scrrun.dll) ' ' Remarks : The name of this function mirrors the FSO method used. '--------------------------------------------------------------------------- Dim objFSO As Object ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Set objFSO = CreateObject("Scripting.FileSystemObject") Select Case objFSO.FolderExists(Path) Case True MyFolderExists = True Case False MyFolderExists = False End Select ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Set objFSO = Nothing End Function

How to use:

  1. Open your document.
  2. Press Alt + F11 to open the Visual Basic Editor (VBE).
  3. Insert a standard module. (Insert -> module)
  4. Paste the code in the window to the right. (F7 to view; CTRL + V to paste)
  5. Save the file.
 

Test the code:

  1. From the VBE, make sure the Immediate Window is visible (CTRL + G)
  2. With your cursor anywhere in the module, press F5. (If your cursor is in the TestMyFolderExists sub procedure, then you won't have to do the next step.)
  3. Select TestMyFolderExists from the dialog box that appears, and then click Run.
 

Sample File:

TestFolderExistsWithFSO.zip 9.54KB 

Approved by mdmackillop


This entry has been viewed 124 times.

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