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
, then that’s probably the directory I want and don’t want to change. However, if I’m in
, then I want to change to my base directory.
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.
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?
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.
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.
Good catch fzz.
JP: I use it so that intellisense bolds the argument I’m on. Pretty good reason, huh?