CODEWITHSUNDEEP
excelvbachdir
Max Pizzimenti
Max Pizzimenti

Reputation: 39

Excel VBA ChDir will not change my directory

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

Upvotes: 1

Views: 2712

Answers (2)

Patrick Holmes
Patrick Holmes

Reputation: 21

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

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 2

Related Questions