Set Path for GetOpenFilename
 

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

Set Path for GetOpenFilename
 

Martin,

Try using

ChDrive "Drive Name"

prior to the ChDir.

HTH,
Bernie
MS Excel MVP

"Martin Los" wrote in message
...
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

Set Path for GetOpenFilename
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin Los" wrote in message ...
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

Set Path for GetOpenFilename
 

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" wrote in message
...
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

Set Path for GetOpenFilename
 

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
-----Original Message-----
Martin,

Try using

ChDrive "Drive Name"

prior to the ChDir.

HTH,
Bernie
MS Excel MVP

"Martin Los" wrote
in message
...
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


.