CODEWITHSUNDEEP
excelvbaoutlook
Waleed
Waleed

Reputation: 919

Open Excel file through Outlook VBA and set focus of it to be in front

I open an Excel file through Outlook VBA but it is in the background (behind the Outlook application).

I tried AppActivate, but it raises this error:

Invalid procedure call or argument

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

Upvotes: 0

Views: 818

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49453

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

Upvotes: 1

Related Questions