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.
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