Excel VBA change default directory for Application.GetOpenFilename

Question

I have googled and found answers to part of my question but not the complete question. I want to use Application.GetOpenFilename in Excel VBA to open a file and I want it to open in the same directory as ThisWorkbook.Path. I have found that beforehand I can do

OpenPath = ThisWorkbook.Path
ChDrive OpenPath
ChDir OpenPath

But, after that runs, if I run any other Application.GetOpenFilename it will still access that same directory (until perhaps I close Excel???). But, I want it to revert back to the default directory (no matter what that was). On my computer, which is Windows XP, it happens to be MyDocuments. But, some of the people using this may have XP and some may have Windows 7. I can't find anywhere how to figure out what the original default directory was so that I can store this so that I can later reset back to the default. Any help would be much appreciated.

@KazJaw suggested a good method. Alternatively you can also use the `SetCurrentDirectory` API too :) — Siddharth Rout, Mar 28 '13 at 15:04

score 6 · Accepted Answer · answered Mar 28 '13 at 14:53

6

So, this could be solution:

Dim StartingDir as string
    StartingDir = CurDir

'...your code here

ChDir StartingDir    'just before you leave

And if necessary do similar with Drive.

answered Mar 28 '13 at 14:53

Kazimierz Jawor

18,861
7
35
55

+ 1 This is the most simple way to do it :) – Siddharth Rout Mar 28 '13 at 15:04

score 4 · Answer 2 · answered Mar 19 '18 at 08:57

I had some struggle answering this question, because ChDir and ChDrive did not work in my network folder. This is what I found in a forum, it's working surprisingly well:

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

Sub SetUNCPath(sPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(sPath)
If lReturn = 0 Then MsgBox "Error setting path."
End Sub

score 1 · Answer 3 · answered Mar 28 '13 at 15:04

1

this could be what you want

dim sStarDir as string
sStarDir=curDir
... do all you stuff
' now reset!
Application.DefaultFilePath=sStarDir

Philip

answered Mar 28 '13 at 15:04

Our Man in Bananas

5,809
21
91
148

When I take away ChDrive and ChDir, and use instead Application.DefaultFilePath, then it just keeps opening in the default MyDocuments and never changes to the directory I want in the first place. Can you provide a bit more detail? – GeoffDS Mar 28 '13 at 18:29
what we're doing there is storing the path when the code starts, then after the line *sStarDir=curDir* that is where you **paste** your code including the code for ChDir, and at the end, AFTER your code has finished, we reset the DefaultFilePath with the code *Application.DefaultFilePath=sStarDir* – Our Man in Bananas Apr 02 '13 at 08:42

score 1 · Answer 4 · edited Apr 11 '18 at 14:09

1

Just put this before the application.getopenfilename():

ChDir "C:"

For example:

ChDir "C:\userjjjj"

myfile = Application.GetOpenFilename()
'Open the file selected
Workbooks.Open (myfile)

edited Apr 11 '18 at 14:09

Jules Dupont

7,259
7
39
39

answered Apr 11 '18 at 12:53

João Álvares Ribeiro

11
3

score 0 · Answer 5 · edited Dec 04 '19 at 00:32

0

Use the next code is working

' declare the variable as string
Dim ruta As String
' get the dir of the current workbook
ruta = ThisWorkbook.Path & "\"
' this line set the dir as the same of the workbook
ChDir ruta 
' open a book in the same directory of the current book
Workbooks.Open(Application.GetOpenFilename)

edited Dec 04 '19 at 00:32

chris neilsen

52,446
10
84
123

answered Dec 04 '19 at 00:14

Jorge O. Gómez

1
1

score 0 · Answer 6 · answered Mar 23 '22 at 12:04

For network paths ChDir doesn't work. The solution of @Buntes-Lama works

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

on VBA 7 systems you need to add the PtrSafe keyword to the Declare statement

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

Explanation: You essentially make the function SetCurrentDirectoryA from the external library kernel32 accessible in your module.

Complete answer to the question (also checking VBA-version via conditional compilation):

#If VBA7 Then
    Private Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
#Else
    Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long
#End If

SetCurrentDirectoryA (ThisWorkbook.Path)  ' Path can be a network directory
Application.GetOpenFilename(...)

Excel VBA change default directory for Application.GetOpenFilename

6 Answers6

Linked