Setting a Base Directory

If you want your Excel app to default to a specific directory when opening or saving files, see Changing the Current Directory. Be sure to read the comments.

I have a slightly different situation. I want my app to open to a specific directory that will give me easy access to sub directories. However, if the current directory is already a sub directory of my base directory, then it’s pretty likely to be in the one I want and I don’t want to change it. For example, my base directory is


If I’m in S:FlashPayroll2010824", then that’s probably the directory I want and don’t want to change. However, if I’m in S:FlashAccountingReporting, then I want to change to my base directory.

Public Sub SetFolderToPayroll()
    If InStr(1, CurDir, “S:FlashPayroll”) = 0 Then
        ChDrive “S:”
        ChDir “S:FlashPayroll”
    End If
End Sub

Pretty simple. If it’s at the base or a sub directory, don’t do anything. If it’s anything else, go to the base directory. Upon further reflection, though, it seems that a more general purpose procedure is in order.

Public Sub SetBaseDirectory(sBase As String)
    If Left$(CurDir, Len(sBase)) <> sBase Then
        ChDrive Left$(sBase, 2)
        ChDir sBase
    End If
End Sub

Now I can call it from multiple locations and pass in the base directory. If the left x characters is the base directory, don’t do anything. Otherwise, change the drive to the first two characters of sBase (e.g. “S:”) and change the directory to the base. It won’t work with UNC paths. Any other problems with it?

Posted in Uncategorized

4 thoughts on “Setting a Base Directory

  1. Potential problem: if the base directory were X:Y and the current directory were X:Y-ABC, the simple string comparison would return a false match and not change the working directory. Robustness requires trailing backslashes.

    Public Sub foo(sBaseDir As String)

      sBaseDir = sBaseDir & IIf(Right$(sBaseDir, Len(sBaseDir)) = “”, “*”, “*”)

      If CurDir & “” Like sBaseDir Then
      End If

    End Sub

  2. If that is a network drive that you have access to it will work. However, if your file has this code in it, and it is given to someone that doesn’t have “rights” it will fail.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.