Search code examples
excelexcel-2007vba

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.

Solution

  • 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.