Open Excel file through Outlook VBA and set focus of it to be in front (not working)-CodePudding

I am using below code to Open Excel file through Outlook VBA and set focus of it to be in front.
The excel file opened without any issue ,But it set on background (behind outlook application).
I tried to use AppActivate, but it raises this error:

Invalid procedure call or argument

Appreciate for any useful comments and answers.

Public Sub Open_Excel_File()
    Dim xExcelFile As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    Dim xExcelRange As Excel.Range
 
    xExcelFile = "xxxxx"   ' Path to the Excel File
 
    Set xExcelApp = CreateObject("Excel.Application")
    Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
    Set xWs = xWb.Sheets(1)
    xWs.Activate
    Set xExcelRange = xWs.Range("A2")
    xExcelRange.Activate
    xExcelApp.Visible = True
    AppActivate xWb.Windows(1).Caption
End Sub

CodePudding user response：

First of all, I'd recommend changing the order in which the Visible property and Activate method are called:

    Set xExcelApp = CreateObject("Excel.Application")
    Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
    Set xWs = xWb.Sheets(1)
    xWs.Activate
    Set xExcelRange = xWs.Range("A2")
    xExcelApp.Visible = True
    xWs.Activate

Typically that is enough.

Second, you may use Windows API functions to bring the window to the foreground. The SetForegroundWindow function brings the thread that created the specified window into the foreground and activates the window.

#If Win64 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" _
               (ByVal hWnd As LongPtr) As LongPtr
#Else
    Private Declare Function SetForegroundWindow Lib "user32" _
               (ByVal hWnd As Long) As Long
#End If

So your code could look like that:

Public Sub Bring_to_front()
    Dim setFocus As Long

    xWs.Activate
    setfocus = SetForegroundWindow(xExcelApp.hwnd)
End Sub