VBA to change current path setting for file open

Posted by Dean B on December 07, 2001 2:00 PM

Is there VBA code that will change the current path setting used in the File Open Dialog box? I want to open several different workbooks in the same directory but don't want to include the path. The workbook names stay the same but the directory will change each month. I want to prompt the user for the path and then set the current path to that directory.

Posted by Dank on December 07, 2001 3:41 PM

Hi,

You can use the simple API function to set the current Windows directory e.g.

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

Sub OpenFile()
Dim strFilename As String
SetCurrentDirectory "C:\temp"

strFilename = Application.GetOpenFilename("Excel file,*.xls")
End Sub

You can also display the Browse for Folder dialog box first, and then display the Open file dialog box if you want but it involves more API functions.

HTH,
Daniel

Posted by Ivan F Moala on December 07, 2001 7:04 PM

Non API

ChDir("your dir name as string")
Then use file open dialog etc.

Ivan

Posted by Ivan F Moala on December 08, 2001 12:24 AM

use danks API call if your path is a UNC dir path
as the chdir won't work on these.

ivan