CODEWITHSUNDEEP
excelexcel-2007vba
GeoffDS
GeoffDS

Reputation: 1271

Excel VBA change default directory for Application.GetOpenFilename

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.

Upvotes: 1

Views: 61558

Answers (6)

ascripter
ascripter

Reputation: 6213

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

Upvotes: 0

Jorge O. Gómez
Jorge O. Gómez

Reputation: 1

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)

Upvotes: 0

João Álvares Ribeiro
João Álvares Ribeiro

Reputation: 11

Just put this before the application.getopenfilename():

ChDir "C:"

For example:

ChDir "C:\userjjjj"

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

Upvotes: 1

Buntes Lama
Buntes Lama

Reputation: 81

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

Upvotes: 4

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

this could be what you want

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

Philip

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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.

Upvotes: 6

Related Questions