XL: ChDir May Fail When Changing to a Root Directory

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c

SUMMARY

In Microsoft Excel, the Visual Basic ChDir statement will fail if the path argument does not contain a backslash character (\). This requirement may cause problems 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 won't get an error message, but the directory won't change.

For example, the following Visual Basic code will fail

   ChDir "C:"

   Run-time error '76':
   Path not found

   ChDir "C:\"

   -or-

   ChDir "C:\EXCEL\LIBRARY"

Visual Basic Code Example

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

This Visual Basic code example displays an input box which asks you which drive or directory you would like to switch to. When you enter a drive or directory, the example checks whether the path you enter contains a backslash and adds one, if needed, before switching to that drive or directory.

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

'----------------------------------------------------------------------

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
'----------------------------------------------------------------------