Set Path for GetOpenFilename

[Martin Los]

I want to set the path to a network for the following
macro:

Sub OpenMultipleFiles()
Dim fn As Variant, f As Integer
Dim MyPath As String

MyPath = "\\network\subdirectory\"
ChDir MyPath
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select one or more files to open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For f = 1 To UBound(fn)
Debug.Print "Selected file #" & f & ": " & fn(f)
Workbooks.Open fn(f)
MsgBox ActiveWorkbook.Name, , "Active Workbook
Name:"
ActiveWorkbook.Close False
' close the active workbook without saving any
changes
Next f
End Sub

However, the GetOpenFilename dialog does NOT go to the
network. Can anybody tell me how to solve this?

TIA

Martin

 

[Bernie Deitrick]

Martin,

Try using

ChDrive "Drive Name"

prior to the ChDir.

HTH,
Bernie
MS Excel MVP

 

[Ron de Bruin]

Hi Martin

'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 "\\DELL\testing"
FName = Application.GetOpenFilename
End Sub

 

[Bernie Deitrick]

Martin,

And if you don't have a drive mapping, try this:

Quote from Tom Ogilvy:

Here is some code frequently posted by Rob Bovey in response to this
question:

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

Public Sub bSetUNCPath(ByVal szPathToSet As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPathToSet)
End Sub


This sets the specified Network Path as the Default.
Then use Application.GetOpenFileName


HTH,
Bernie
MS Excel MVP

 

[Martin Los]

Thanks Ron and Bernie for your contributions!

With the drive mapping I managed to solve it really
easily. I have put

ChDrive "k"

where k = "\\network\subdirectory"

The Functions Ron mentioned to might work better in the
future if I want to export the macro to other users that
are not using drive mapping.

Great help from both of you!

Both of your