How to set GetOpenFilename to a specific Folder

[Corey]

Sub Button104_Click()
Application.DefaultFilePath = "\\Server\server\Reports"
Application.GetOpenFilename ("PDF Reports (*.pdf), *.pdf")
End Sub


I am using the code above to try to always open the folder Reports on a server.

However if i select another folder, next time the code is run the previously selected folder is
opened.

How can i code this to ALWAYS open the specified folder ONLY ?


Corey....

 

[NickHK]

If you want to limit the user to that folder, you will need the to use the
API version with the OFN_NOCHANGEDIR flag:
http://vbnet.mvps.org/code/comdlg/fileopendlg.htm

Otherwise you could just use a list box and Dir( ) to it with the files
yourself.

If you mean that you always want to start with that folder, but the user can
navigate elsewhere, then set the CurDir before you call that function.

NickHK

 

[Corey]

Nick,

How do i do that :

"set the CurDir before you call that function."
I do not know what CurDir is:
Is that Current Directory?

I though the Application.DefaultFilePath = "\\Server\server\Reports" does that ?


Corey...


If you want to limit the user to that folder, you will need the to use the
API version with the OFN_NOCHANGEDIR flag:
http://vbnet.mvps.org/code/comdlg/fileopendlg.htm

Otherwise you could just use a list box and Dir( ) to it with the files
yourself.

If you mean that you always want to start with that folder, but the user can
navigate elsewhere, then set the CurDir before you call that function.

NickHK

 

[NickHK]

That is only initially. You will probably navigate away from that during the
course of your Excel session.
Check out CurDir and CurDrive in the help

NickHK

 

[Dave Peterson]

Saved from a previous post:

If you refer to the folder\file by its UNC path
(\\something\somethingelse\filename.xls), you can use an API call. In fact,
this works with mapped drives, too:

Here's a sample, but with getsaveasfilename.

Option Explicit
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


Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub