Specify Start Location for GetOpenFileName box

[Guest]

Can you specify the start location for the getopenfilename dialog box?

Dim f As String
f = Excel.Application.GetOpenFilename("LIS Files (*.lis), *.lis")

I would like to start the search in a specific location, instead of "My
Documents". Couldn't find any info on this topic yet.

Thanks!

 

[Ron de Bruin]

Hi WCR

Try this for C:\Data

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

SaveDriveDir = CurDir

MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

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

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

 

[Guest]

Thanks Ron - I was able to get it to work with a local folder, but it will
not work with a shared network folder. I can access the network folder by
typing in the path, but it won't work from within the macro. Any ideas?

old_path = CurDir
path = "\\network_computer\shared_drive"

ChDrive path
ChDir path

f = Excel.Application.GetOpenFilename("LIS Files (*.lis), *.lis")
Cells(2, 3) = f

ChDrive old_path
ChDir old_path

 

[Ron de Bruin]

Hi

Use this then

'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

 

[Ken Valenti]

This seems to works too

const TheFolderPath = "\\Anywhere"
Application.GetOpenFilename(filefilter:="Excel Files (*.xls)," &
TheFolderPath & "\*.xls")