Changing the current directory

One common question that I see in the newsgroups is “how can I force Application.GetSaveAsFileName or Application.GetOpenFileName to open in a specific directory ?”. The answer to this is use the ChDrive and ChDir functions.

If you want to open the dialog in say “E:My files”, then this code should do it

Sub Test()
   Dim Filename As Variant
   ChDrive “E”
   ChDir “E:My files”
   Filename = Application.GetSaveAsFilename()

   If TypeName(Filename) <> “Boolean” Then
      MsgBox Filename
   End If
End Sub

However, this doesn’t work if your path is in a network folder, like

\SomeServerSomefolder

to do this, you need to use the SetCurrentDirectory API function, which is defined as:

Declare Function SetCurrentDirectory Lib “kernel32” Alias “SetCurrentDirectoryA” (ByVal lpPathName As String) As Long

Now you can change the current path no matter if the path is mapped to a drive or not. You can also extend this example, saving the current path temporarily, to avoid messing with the user’s settings.

Sub TestAPI()
   Dim CurrentPath As String
   Dim Filename As Variant
   
   ‘Store the current path
  CurrentPath = CurDir
   
   ‘Change the path to the one we want
  SetCurrentDirectory “\SomeServerSome Path”
   
   ‘Ask for the file name
  Filename = Application.GetSaveAsFilename()

   If TypeName(Filename) <> “Boolean” Then
      MsgBox Filename
   End If
   
   ‘Change the path back
  SetCurrentDirectory CurrentPath
End Sub

Posted in Uncategorized

11 thoughts on “Changing the current directory

  1. How’s about:

    On Error Resume Next
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    On Error GoTo 0

    Works every time.

  2. Thanks for the hints – it’s been a regular source of annoyance that the dialogs opened up in the “wrong” folder every time.

    A (somewhat) related question for me would be can you convert a mapped drive to the UNC name (ie E: = \ServerSomePath )? Is this another API function?

    Tony

  3. headtoadie –

    The GetOpenFileName method gives you more control over the process. All it tells the program is the name or names of the selected files. Your program is then allowed to do with these files what you want it to, usually open them, but not always.

  4. Thanks Rob, that wasn’t quite what I wanted but the function WNetGetUniversalName from the same group of pages does better what I need to do. The shares are typically already mapped to a drive letter, it’s just I can never assume that it’s the same letter on each PC so I was hoping for something to let me check it.

  5. I would like to be able something corresponding to

    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path

    (to get the relative path)

    when klicking on a template (.xlt-file). (I am forced to use the xlt extension by my boss.) These templates all extract the same information from an .xls file residing in the same folder as the templates. Problem is, I need to use relative paths since these folders will reside in completely different locations in every computer and network where they’ll be used. When starting from say template.xlt, you get a new workbook – “template1? and this new project doesn’t seem to have a “home” yet.

    So my problem again (or maybe my dilemma): I want a template1 file (that is started by double clicking a template.xlt file) to be able to find the folder of the template.xlt from which it is started, because in this folder we have a data file with info that the template1 needs to get in order to work properly.

  6. I tried the VBA code to change the current directory to a network path, my company’s corporate shared folders. I was only able to get it working once I had made the function private by declaring it as private like so:

    Private Declare Function SetCurrentDirectory Lib “kernel32? Alias “SetCurrentDirectoryA” _
    (ByVal lpPathName As String) As Long

    Cheers
    Mahtab

Leave a Reply

Your email address will not be published. Required fields are marked *