How to set default directory for Excel's GetOpenFilename using Outlook VBA?

Question

I'm trying to set the default directory for the VBA function GetOpenfilename. I managed to get it working before but lost the code before saving it.

Sub Sample2()
    Dim myFile As Variant
    Dim i As Integer
    Dim myApp As Excel.Application
    Dim strCurDir As String
    Set myApp = New Excel.Application

    ChDrive ("H:\")
    ChDir ("H:\99 - Temp")

    'Open File to search
    myFile = myApp.GetOpenFileName(MultiSelect:=True)

    If myFile <> False Then
        If IsArray(myFile) Then  '<~~ If user selects multiple file
            For i = LBound(myFile) To UBound(myFile)
                Debug.Print myFile(i)
            Next i
        Else '<~~ If user selects single file
            Debug.Print myFile
        End If
    Else
        Exit Sub
    End If

End Sub

I tried several variations of this code and the posts I found are very old. It is going to be part of a bigger code in Outlook 2016.

Answer 1

Try the FileDialog property of the Excel object instead...

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

Dim myFile As Variant
With xlApp.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .ButtonName = "Select"
    .Title = "Select File"
    .InitialFileName = "H:\99 - Temp\"
    If .Show = 0 Then Exit Sub 'user cancelled
    For Each myFile In .SelectedItems
        Debug.Print myFile
    Next myFile
End With

Set xlApp = Nothing