Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set a default path when using Application.GetOpenFilename | Excel Programming | |||
How do I set a default path when using Application.GetOpenFilename | Excel Programming | |||
GetOpenFilename Dialog default path | Excel Programming | |||
path names in getopenfilename | Excel Programming | |||
GetopenFilename default path | Excel Programming |