How do I set the default path for the GetOpenFilename dialog to a network share?

[Randy]

I have developed some Excel VB modules that update a workbook with data from text files. In order to have the macro use the directory of the last file(s) used to update the workbook, I saved the full filename of the last file in a workbook name. And then, when the user wished to update the workbook with new files, I read the name and use the ChDrive and ChDir commands to set the CurDir before calling the Application.GetOpenFilename procedure to have the user select the new files to use to update the workbook. This works great if the user selects a file using a drive or mapped drive where the full filename is in the form

D:\dir1\file1.ext

but, if the user selects the files using a network share, the full filename is in the form

\\network_share\dir1\file1.ext

And, the ChDrive function does not work. The ChDir function does not fail, but since it does not change the drive, the GetOpenFilename routine uses the current directory for whatever drive letter is current. I can not find a way to determine the current drive letter, but the function CurDir returns the current directory. Somehow, Excel is able to keep the CurDir set to a network share and unless it is changed, the GetOpenFilename will default to that folder. You can test this yourself by opening a file (use File:Open) on a network share, then go to an immediate window in VB and type "print curdir". It will respond with a current directory in the format for a network share as above. Then click the File:Open again and the default directory will be that folder. But, if you type ChDrive("C") in the immediate window and then click File:Open, it will default to some other directory on your C drive.

So, is there any other way to set the current directory and drive that allows a network share folder to be specified instead of a drive letter for the GetOpenFilename dialog?


Or, in other words, is there any way to get the GetOpenFilename dialog to default to a network share folder?

Thanks for your help,
Randy

 

[Randy]

Dick,

Thanks. I knew there had to be a simple solution. I just couldn't find it. This solution works great and can even be used for non-UNC paths, so I no longer need to use ChDir/ChDrive.