Reputation: 919
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
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