Opening a directory using code

[Robert Pollock]

Using the following I can open a window showing the files in that directory;

Application.Dialogs(xlDialogOpen).Show "\\Rfp_main\rfp2005\templates"

However if someone tries to load a file ... say Word.. in that directory,
excel tries to import it into the open workbook. How can I rempve the focus
back to the workbook and close it, thus leaving only the open window on the
desktop with excel now closed down ?

Thanks

 

[Tom Ogilvy]

You showing the Excel file=>Open dialog, just as if it were select from the
menus - so you can't divorce its built in functionality.

Try using Application.GetOpenFileName (that displays the same dialog) which
just returns a string with the fully qualified path and name of the file
selected. Then you can have your code take the appropriate action. You
would need to change the default directory to control which directory it
opened to.

For local drives you can use Chdrive and Chdir. for a network directory
(code originally 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)
Debug.Print lReturn
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Example Usage:

Sub FindFile()
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
Application.Dialogs(xlDialogFindFile).Show
End Sub