excelvbaoutlook-2016getopenfilename

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


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.


Solution

  • 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