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:FlashPayroll2010 824", 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.
If InStr(1, CurDir, “S:FlashPayroll”) = 0 Then
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)
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?