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

S:FlashPayroll

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?

4 thoughts on “Setting a Base Directory”

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

Why do you use the (optional) first parameter for the InStr Function?

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.