MS-Excel / Functions and Formula

---

Using File-Management Functions

The following sections demonstrate how to use a couple of key VBA file-management functions: the Dir function, which you use to find out whether a file exists, and the CurDir function, which returns the current path.

Using the Dir Function to Check Whether a File Exists

Often when managing files, you'll need to first check whether a particular file already exists. For instance, if you're about to save a file, you may want to make sure the save operation won't overwrite an existing file - a file with the same name in the same location on the hard drive.

Or if you're about to open a file, you may want to see if that file exists before you use the Open method; otherwise, VBA will give an error.

To test whether a file exists, you can use a straightforward procedure such as the one shown in Listing below.

Checking if a file exists with the Dir function

1. Sub Does_File_Exist()
2.     Dim strTestFile As String, strNameToTest As String, _
	   strMsg As String
3.     strNameToTest = InputBox("Enter the file name and path:")
4.     If strNameToTest = "" Then End
5.     strTestFile = Dir(strNameToTest)
6.     If Len(strTestFile) = 0 Then
7. 	  strMsg = "The file " & strNameToTest & _
		" does not exist."
8.     Else
9. 	  strMsg = "The file " & strNameToTest & " exists. "
10.    End If
11.    MsgBox strMsg, vbOKOnly + vbInformation, _
	  "File-Existence Check"
12. End Sub

This procedure in Listing above uses the Dir function to check whether a file exists and displays a message box indicating whether it does or doesn't. This message box is for demonstration purposes only. In a real-world macro you'd likely use the result of the test to branch (execute different code blocks) based on whether the file exists.

Here's how the code works:

• Line 2 declares the string variables strTestFile, strNameToTest, and strMsg.
• Line 3 then displays an input box prompting the user to enter a filename and path; VBA assigns the result of the input box to strNameToTest.
• Line 4 compares strNameToTest to a blank string (which means the user clicked the Cancel button in the input box or clicked the OK button without entering any text in the text box) and uses an End statement to end the procedure if it gets a match.
• Line 5 assigns to strTestFile the result of running the Dir function on the strNameToTest string. If Dir finds a match for strNameToTest, strTestFile will contain the name of the matching file; otherwise, it will contain an empty string.
• Line 6 begins an If... Then statement by testing the length of the strTestFile string. If the length is 0, the statement in line 7 assigns to strMsg text saying that the file doesn't exist; otherwise, VBA branches to the Else statement in line 8 and runs the statement in line 9, assigning text to strMsg saying that the file does exist. Line 10 ends the If statement.
• Line 11 displays a message box containing strMsg. Line 12 ends the procedure.

The code shown in Listing above isn't bulletproof because Dir is designed to work with wildcards as well as regular characters. As long as you're working with a simple text filename in strNameToTest, you'll be fine because Dir compares that text to the existing filenames on the hard drive and the result lets you know whether you have a match. But if strNameToTest contains wildcards (say it's c:\temp\*.*, the asterisks specifying any filename), Dir reports that the file exists. However, there's no file by that name, just one or more files that match the wildcard. You can check on line 5 whether the name returned by Dir is exactly the same as the input name and make sure you do a case-insensitive comparison. This literalness of Dir is a nice illustration of GIGO (garbage in, garbage out) - from the computer's (and VBA's) point of view, it's doing what you asked it to, but the result is far from what you intended.

Returning the Current Path

You can find out the current path (the location on the hard drive to which the host application is currently pointed) on either the current drive or a specified drive by using the CurDir function. Often, you'll need to change the current path (using the ChDir function) to make sure the user is saving files in, or opening files from, a suitable location.

To return the current path, use CurDir without an argument:

CurDir

To return the current path for a specified drive, enter the drive letter as an argument. For example, to return the current path on drive D, use this statement:

CurDir("D")