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
– Siddharth Rout, Commented Mar 28, 2013 at 15:04

Kazimierz Jawor · Accepted Answer · 2013-03-28 14:53:03Z

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, 2013 at 14:53

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

+ 1 This is the most simple way to do it :)

Buntes Lama · Accepted Answer · 2018-03-19 08:57:24Z

4

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

answered Mar 19, 2018 at 8:57

Buntes Lama

815 bronze badges

Comments

Our Man in Bananas · Accepted Answer · 2013-03-28 15:04:49Z

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, 2013 at 15:04

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

2 Comments

GeoffDS Over a year ago

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?

Our Man in Bananas Over a year ago

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

Jules Dupont · Accepted Answer · 2018-04-11 14:09:05Z

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, 2018 at 14:09

Jules Dupont

7,6457 gold badges43 silver badges41 bronze badges

answered Apr 11, 2018 at 12:53

João Álvares Ribeiro

113 bronze badges

Comments

chris neilsen · Accepted Answer · 2019-12-04 00:32:20Z

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 4, 2019 at 0:32

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

answered Dec 4, 2019 at 0:14

Jorge O. Gómez

11 bronze badge

Comments

ascripter · Accepted Answer · 2022-03-23 12:04:02Z

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(...)

Collectives™ on Stack Overflow

Excel VBA change default directory for Application.GetOpenFilename

6 Answers 6

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related