GetOpenFileName

[Gary S]

Hello,

When using "Application.OpenFileName" I want to be able to do two things
with it:
1. Open up to a specific directory, not the current directory.
2. Not open Excel files but Access files.

I would greatly aprreciate anyones help.

 

[Mike H.]

ChDir "n:\public\chart of accounts"
Let TheFile = Application.GetOpenFilename("xls files, *.xls")

The above code would display the .xls files in the path you specify. So for
Access, just change the extension and this should do it.

 

[Ron de Bruin]

Use also ChDrive

This example will remember the current path so it can restore it when the macro is ready

Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)

' do your stuff

wb.Close False 'Or True if you want to Save
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

 

[Gary S]

Mike,
That ChDir didn't seem to make any difference,
the file dialogue box still opened to mydocuments directory.

 

[Mike H.]

Makes no sense. I tested it with several different sub-dir's and it
continued to display the appropriate sub-dir's .xls files for me.

 

[Ron de Bruin]

See my reply