XL2000: ChDir May Fail When Changing to a Root Directory

In Microsoft Excel, the Visual Basic ChDir statement fails if the path argument does not contain a backslash character (\). This requirement may cause issues if you use ChDir to change to a root directory, because the path of a root directory does not necessarily contain a backslash.

NOTE: When you use Microsoft Excel version 7.0 or later, you do not receive an error message, but the directory does not change.

For example, the following Visual Basic code fails

ChDir "C:"
				

and you receive the following error message: The following lines of code succeed because they each contain a backslash:

   ChDir "C:\"
				

   ChDir "C:\EXCEL\LIBRARY"
				

It is possible to incorporate error-handling into a ChDir function to prevent an error from occurring. The following sample Visual Basic code demonstrates one way to do this.

Visual Basic Code Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: This sample code displays an input box, which asks you to select a drive or directory to switch to. When you enter a drive or directory, the code determines whether the path that you enter contains a backslash and adds one, if needed, before switching to that drive or directory.

To run the sample code, position the insertion point in the line that reads "Sub MainMacro()," and then press F5.

Option Explicit

Sub MainMacro()
   'Dimension some variables.
   Dim NewDir As Variant
   'Prompts you to enter a drive/directory. For example, D: or C:\EXCEL.
   'If you type in an invalid directory, the subroutine will fail. For
   'example, typing just "C" (without the quotes) is not going to work.
   NewDir = InputBox("Switch to which drive/directory?")
   'If the NewDir ends in a colon, indicating it is a root directory,
   'concatenate a backslash onto the end of it. For example, C: would
   'become C:\. If you actually enter "C:\", the subroutine doesn't add
   'another backslash.
   If Right(NewDir, 1) = ":" Then
      NewDir = NewDir & "\"
   End If
   'Switches to the proper drive (the first letter in NewDir: 'Left'
   'gives us this) and directory.
   ChDrive Left(NewDir, 1)             'change to the drive (C:, etc.)
   ChDir NewDir                        'change to the directory
   'Display the name of the current directory so you know it worked.
   MsgBox "Current directory is " & CurDir()
End Sub
				

The If-End If routine is the error-checking procedure. Whenever you use a ChDir statement, preceding it with the If-End If routine (or some variation thereof) can help prevent the "no backslash" error.