VBA ChDrive/ChDir does not work if the file is saved

userYyoE 0
Viewed 44

If you run the following VBA Code in any Office application Word/Excel/Access it does not change the default path if the file in which the code resides is saved:

I've tested it on Windows 10, MS-Office 2010 x86, 2016 Plus x86 and O365 x64

  1. For example create a new Excel file without saving it!

  2. Put the following code in a module or worksheet/workbook-module.

  3. Run the code The result is: the dialogbox opens your windows-user folder.

  4. Now save the file somewhere.

  5. Run the code again. The result is: the dialogbox does not opens your windows-user folder.!

You could do the test with some other folders/drives. The result is the same - at least on my PCs.

Sub ChangeDefaultPath()

' For me are these two lines of code importent.
' Why they do not work as they should.
ChDrive "C"
ChDir "C:\Users\" & Environ("USERNAME")

' This line of code is just for testing. 
' You could comment it out and use F12 or save as from the application menue manually.
Application.Dialogs(xlDialogSaveAs).Show

End Sub

Is this issue known? Is there a fix for that without workarounds?

Thanks for any hints and helps.

ms-office ms-access vba ms-word excel
1 Answers

I'M not sure but there is a difference between curdir and Application.DefaultFilePath

Sub ToCheck()
Dim s As String
s = Application.DefaultFilePath
Debug.Print s

ChDrive "C:\"
ChDir "C:\Users\" & Environ("USERNAME")
Debug.Print CurDir

Application.DefaultFilePath = "C:\Users\" & Environ("USERNAME")
Debug.Print Application.DefaultFilePath
' Now check a saving dialog or anything else


' Set back
Application.DefaultFilePath = s ' Set back
End Sub
Perry415780
Related