Userform Local Drive & Network drive question

[Joel Mills]

I have a userform with two command buttons assigned to the following macros.
The user should be able to run the appropriate macro depending on the
location of a file, either on the local drive "C:\" or the Network drive.
After changing the current drive and running the macro a second time the
drive remains the one previously selected. Could someone please revise this
code to select the location of the Lotus File?


Sub ExportLocal()
CurDrive = "C:\"
ChDir "C:\P3WIN\PROJECTS"
Dim MyFile As String
MyFile = Application.GetOpenFilename("Lotus 1-2-3 Files (*.wk?),*.wk?")
'Stop here if all you want is the path and file name - it's in the
'MyFile variable. 'Continue if you want to open it.
If MyFile = False Then End
Workbooks.Open (MyFile)
End Sub
Sub ExportNetwork()
CurDrive = "\\Bayltdest\Common2\Planning"
ChDir "\Shared Projects"
Dim MyFile As String
MyFile = Application.GetOpenFilename("Lotus 1-2-3 Files (*.wk?),*.wk?")
'Stop here if all you want is the path and file name - it's in the
'MyFile variable. 'Continue if you want to open it.
If MyFile = False Then End
Workbooks.Open (MyFile)
End Sub

 

[Dave Peterson]

Your code is pretty much the same thing for each option.

Maybe you could just have a single button that asks what folder should be used:

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 ExportEither()
Dim Resp As Long
Dim NetworkPath As String
Dim LocalPath As String
Dim MyFile As Variant
Dim CurDriveFolder As String

NetworkPath = "\\Bayltdest\Common2\Planning\Shared Projects"
LocalPath = "C:\P3WIN\PROJECTS"

CurDriveFolder = CurDir

Resp = MsgBox(prompt:="Hit Yes for Local" _
& vbLf & "No for Network" & vbLf & "Or Cancel", _
Buttons:=vbYesNoCancel)

On Error Resume Next
If Resp = vbCancel Then
Exit Sub
ElseIf Resp = vbYes Then
ChDirNet LocalPath
Else
ChDirNet NetworkPath
End If
If Err.Number <> 0 Then
MsgBox "error changing folder"
Err.Clear
Else
MyFile = Application.GetOpenFilename("Lotus 1-2-3 Files,*.wk?")
If MyFile = False Then
'do nothing
Else
Workbooks.Open MyFile
End If

ChDirNet CurDriveFolder
End If

End Sub

The ChDirNet is an API function that works when you change to a network
path/folder. But it works with local drives, too.

 

[Guest]

'HOW TO CHANGE DRIVE AND FOLDER AS DEFAULT WHEN
' OPENING FILES WITH GETOPENFILENAME AS WELL AS
' HOW TO MAKE A UNC A DEFAULT BECAUSE CHDIR AND CHDRIVE
' DO NOT WORK WITH UNC PATHS.


'Public Sub OpenFile()
' Dim ProperPath As String
' Dim fName As Variant
' Dim wkbk As Workbook
'
' 'Save the current folder path
' ProperPath = CurDir
'
' 'Change the folder path
' ChDrive "C"
' ChDir "C:\My Path\My Folder"
'
' 'Display the open dialog box
' fName = Application.GetOpenFilename(fileFilter:="Excel Files
(*.xls),*.xls")
'
' Set wkbk = Workbooks.Open(fName)
'
' 'Change the default folder
' ChDrive ProperPath
' ChDir ProperPath
'
' Set wkbk = Nothing
'End Sub




'The 'ChDrive' and 'ChDir' functions don't understand UNC paths. There's a
very simple
'windows API function, however, that will set the current directory to a UNC
'path.
'
'Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal
'lpPathName As String) As Long
'
'Sub SetUNCPath()
' Dim lRet As Long
' lRet = SetCurrentDirectoryA("\\SERVER1\Downloads\")
' If lRet = 0 Then Err.Raise 9999, , "Error setting path."
'End Sub
'
'--
'Rob Bovey, MCSE
'The Payne Consulting Group

 

[Joel Mills]

Dave,thanks for the help. I thought a userform would be the right approach,
but this is a much cleaner soliton.

Your code is pretty much the same thing for each option.
Dave,thanks for the help. I thought a userform would be the right

approach, but this is a much cleaner soliton.