Excel VBA ChDir will not change my directory

Question

I am trying to have a user file selection box automatically open up to a directory.

Everything works fine, but instead of opening to the correct directory, I still have to click through to the right one.

My code is as follows.

ChDir ("\\file path string")
userFile = Application.GetOpenFilename(fileFilter:="csv Files(*.csv),*.csv", Title:="csv Files")
Workbooks.OpenText Filename:=userFile

I have also been able to get anything like ChDrive to work. The file is on a network.

Thank you

Are you including a \ as the last character of your `file path string` ? — Samuel Everson, Jul 21 '21 at 22:36
`ChDir` will not change a *drive*, only to a different folder on the same drive as the current directory. — Tim Williams, Jul 21 '21 at 22:43

score 1 · Answer 1 · answered Jul 21 '21 at 22:49

1

I use this when I need to set the current directory to a network share:

Option Explicit

Private Declare Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
    Dim lReturn As Long
    lReturn = SetCurrentDirectoryA(szPath)
    If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub tester()

    ChDirNet "\\marge\bart\"

End Sub

answered Jul 21 '21 at 22:49

Tim Williams

154,628
8
97
125

Thank you for the response. I am relatively new to VBA and I cannot get this to work for me. Can you help me decipher this or provide a simpler solution. – Max Pizzimenti Jul 22 '21 at 19:49
What exactly happens when you try it? – Tim Williams Jul 22 '21 at 20:15
Simply copying a running stops me with a compile error that says the code must be updated for use on 32 bit systems. It also says I should review and update Declare Statements and mark them with PtrSafe attributes. – Max Pizzimenti Jul 22 '21 at 21:19
1

https://stackoverflow.com/questions/16904231/vba-excel-setcurrentdirectory-using-activeworkbook-path-does-not-work-with-64 – Tim Williams Jul 22 '21 at 22:24

score 0 · Answer 2 · answered Jul 22 '21 at 23:07

There is a missing keyword, "PtrSafe" in the sample code for it to work on 64-bit versions of Office. The code should look like this

Option Explicit

Private Declare PtrSafe Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
    Dim lReturn As Long
    lReturn = SetCurrentDirectoryA(szPath)
    If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub tester()

    ChDirNet "\\marge\bart\"

End Sub

Excel VBA ChDir will not change my directory

2 Answers2

Linked