Default Folder with OpenDialog

[lopiteaux]

Hi,
I have an OpenDialog that needs to be executed prior to executing a bit of code. I currently have it down as follows:

    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
'WF1.1
'OpenDialog Parameters:
    'Set File Filters (File Types that can be opened/viewed via the OpenDialog)
    Filter = "Excel Files (*.xls),*.xls," & _
        "Comma Separated Files (*.csv),*.csv," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
    FilterIndex = 2 ' Defaults the File Filter to *.csv.
    Title = "Please Select the Relevant Report:" 'OpenDialog caption.
    With Application
        Filename = .GetOpenFilename(Filter, FilterIndex, Title) 'Creates the OpenDialog box with the defined criteria.
        ChDir (.DefaultFilePath) 'Resets the Default Pathname.
    End With
'ErrorHandler - Exits the SubRoutine if no File is selected:
    If Filename = False Then
        msgtitle = "DefaultTitle"
        msgbody = "No file was selected."
        msgbody = msgbody & vbNewLine & vbNewLine & "Please try again."
        msgconfig = vbOKOnly + vbCritical
        msgans = MsgBox(msgbody, msgconfig, msgtitle)
        Exit Sub
    End If
    
'Open the specified file:
    Workbooks.Open Filename

This works great, except for one thing - the default pathname. I am working on a network, and cannot get it to revert to a default pathname, which is written in the following format:

[/URL][URL="file://abcde12345/Path/Path/Path"]\\abcde12345\Path\Path\Path[/URL][URL="file://\\abcde12345\Path\Path\Path"]

Any thoughts?

C

 

[Andrew Poulsom]

Have you tried using ChDrive and ChDir to navigate to that path before showing the dialog?

 

[lopiteaux]

Have you tried using ChDrive and ChDir to navigate to that path before showing the dialog?

Hi Andrew, thanks for getting back to me. I have indeed yes, and have found that this works when specifying the actual mapped pathname (e.g. ChDrive ("Q")), but not when wanting to specify the network path ("\\abcde12345"), which I would have to do in order to make the code usable for everyone in my team.

Any thoughts?

C.

 

[lopiteaux]

Andrew - I've tried adding the ChDrive routine but cannot get it to work with the \\abcde12345 network drive format... will you be able to assist?

 

[Andrew Poulsom]

ChDrive doesn't support UNC paths. You will need to use the Windows API. See Ivan Moala's post here:

http://www.mrexcel.com/forum/showthread.php?t=4035

 

[ZVI]

Code for Excel 64-bit compatibility:

#If Win64 Then
  Private Declare PtrSafe Function SetCurDir Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long
#Else
  Private Declare Function SetCurDir Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long
#End If

Sub Test()
  'SetCurDir "\\Server1\Common\Tmp"
  SetCurDir ThisWorkbook.Path
End Sub

 

[lopiteaux]

ChDrive doesn't support UNC paths. You will need to use the Windows API. See Ivan Moala's post here:

http://www.mrexcel.com/forum/showthread.php?t=4035

Brilliant Andrew, thanks a million for setting me on the right path!

C.