Excel FilePicker dialogbox in Outlook code opens in background

Question

I developed an Outlook macro that lets the user choose and open an Excel file.

The file dialog box opens in the background. To the user it looks like the Outlook application is stuck whereas its just waiting for the user to choose a file in the Filepicker dialog box behind all other windows.

Dim SigFolder2 As String
Dim fd As Office.FileDialog
Dim selectedItem As Variant
Dim ExcelFileName As String
Dim FileName As String
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

'Suggested Folder--Downloads
SigFolder2 = "C:\Users\" & Environ("UserName") & "\Downloads\"

'Dialog Settings

Set fd = objExcel.FileDialog(msoFileDialogFilePicker)
With fd
    .Filters.Clear
    .InitialFileName = SigFolder2
    .AllowMultiSelect = False
    .Title = "Select Signature File"
End With

'Getting the file
If fd.Show = -1 Then
    For Each selectedItem In fd.SelectedItems
        SigFolder = selectedItem
    Next
Else
    Exit Sub
End If

ExcelFileName = SigFolder
FileName = Left(fso.GetFileName(ExcelFileName), InStr(fso.GetFileName(ExcelFileName), ".") - 1)
Debug.Print ExcelFileName 'file Path with filename

Is there any way to correct this?

Answer 1

The issue is caused by the fact that Excel windows don't know anything about Outlook windows. You must set a parent window to your child window dialog if you want to keep it always on top of another window. For example:

Public Declare Function SetForegroundWindow _
Lib "user32" (ByVal hwnd As Long) As Long

Public Sub Bring_to_front()
    Dim setFocus As Long

    ThisWorkbook.Worksheets("Sheet1").Activate
    setfocus = SetForegroundWindow(Application.hwnd)
End Sub

In your case it will be a dialog window:

Private Declare Function FindWindowA Lib "user32" (ByVal class As String, ByVal caption As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal win As Long) As Long

Dim hxl As Long

Set objExcel = New Excel.Application
Set fd = xl.FileDialog(msoFileDialogFilePicker)
hxl = FindWindowA("XLMAIN", "Excel")
If (hxl <> 0) Then
    res = SetForegroundWindow(hxl)
End If
With fd
.Filters.Clear
 .InitialFileName = SigFolder2
 .AllowMultiSelect = False
 .Title = "Select Signature File"
End With
res = fd.Show