CODEWITHSUNDEEP
excelvbaoutlookfilepicker
Amrita Deb
Amrita Deb

Reputation: 335

Excel FilePicker dialogbox in Outlook code opens in background

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?

Upvotes: 0

Views: 1339

Answers (2)

pstraton
pstraton

Reputation: 1120

Use of the BringWindowToTop API function is a simple solution:

#If VBA7 Then
    Private Declare PtrSafe Function BringWindowToTop Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Private Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long
#End If
    
    '...
    
    Dim objExcel As Object
    Set objExcel = New Excel.Application
    Call BringWindowToTop(objExcel.hwnd) 'Will force the Excel application's (eventually-opened)
                                         'dialog window to the top of the Z-stack and display it.
    
    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
    
    '...

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49405

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

Upvotes: 1

Related Questions