Change directory before File->Open

[Guest]

H all,

I have list of files whose file names changes everyday, like :
filename_mmddyy.xls where they reside in a directory structure like:
\\server\root\YYYY\mm-yyyy\

I would like to direct Excel to go the specified directory and bring up the
File->Open dialog box and let the user select the file then allow the code to
continue after the right file is opened.

I am trying to eliminate the need for having the users to navigate to the
directory and select the file. I would like to navigate there first and
then let the users select the file. I tried using the ChDir command first
and then use the [Application.GetOpenFilename] command but it didn’t go to
the right directory first. Can you share some suggestions? Thanks in
advance.


Ben

 

[Ron de Bruin]

Hi Ben

'Previously posted by Rob Bovey:

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

' sample usage

Sub FindFile()
Dim FName As Variant

ChDirNet "\\JELLE\YourFolder"
FName = Application.GetOpenFilename
End Sub


See also example 6 where I use the ChDirNet function
http://www.rondebruin.nl/copy3.htm#select