Invoked Application on top of screen

[Joakim N]

Hi,



I'm using Excel to draw my (quite complicated) reports invoked from Access. However I don't always get Excel Application on top of screen after code is executed. Anyone knows how to ensure that Excel Window is on top of screen after execued code?

Public gappExcel As Excel.Application

Sub Test()
    '...
    If gappExcel Is Nothing Then Set gappExcel = New Excel.Application
    gappExcel.Visible = True
    '...
End Sub

[ranman256]

DoCmd.RunCommand acCmdAppMinimize
If gappExcel Is Nothing Then Set gappExcel = New Excel.Application
gappExcel.Visible = True

[Joakim N]

Thanks,

That worked fine.

One follow up question. If I have 2 screens:
- Excel and Access are on distinguished screens. DON'T MINIMIZE ACCESS
- Excel and Access are on same screens. MINIMIZE ACCESS

Any idea how to identify which screen the application is visible?

I found the property .Left in Excel applcation. Maybe possible to use. But I couldn't find .Left in Access application. Any idea how to find the position of Access application?

(I use a Windows API to get the screen resolution for my comparison)

Dim dLeftAccess As Double
Dim dLeftExcel As Double

dLeftExcel = gappExcel.Left
'dLeftAccess = ?

If gappExcel Is Nothing Then Set gappExcel = New Excel.Application
gappExcel.Visible = True

'Hide Access?
'if MY_CONDITION then
    DoCmd.RunCommand acCmdAppMinimize
'End If

[Joakim N]

Hi,

I solved it by using API-functions in following order:
1. Excel application on top of screen
2. Workbook on top of screen

I have attached the code if anyone is interested.

'Show Application/Window on top of screen
Private Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long

'Find a child window with a given class name and caption
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) _
As Long

Public gappExcel As Excel.Application

Sub ExcekWkbkOnTopOfScreen(sWkbName As String)

    Dim lhWndExcelApp As Long
    Dim lhWndExcelWkb As Long
    
    If Not gappExcel Is Nothing Then
        
        'Excel application on top
        lhWndExcelApp = gappExcel.hwnd
        BringWindowToTop (lhWndExcelApp)
        
        'Wkb on top
        lhWndExcelWkb = WorkbookWindowhWnd(gappExcel.Windows(sWkbName))
        BringWindowToTop (lhWndExcelWkb)
    End If
End Sub

Function WorkbookWindowhWnd(ByRef wndWindow As Window) As Long
'Get handle of Excel Workbook

    Dim lhWndExcel As Long
    Dim lhWndDesk As Long

    'Get the main Excel window
    lhWndExcel = gappExcel.hwnd

    'Find the desktop
    lhWndDesk = FindWindowEx(lhWndExcel, 0, "XLDESK", vbNullString)

    'Find the workbook window
    WorkbookWindowhWnd = FindWindowEx(lhWndDesk, 0, "EXCEL7", wndWindow.Caption)
End Function

Sub Test()
    Call ExcekWkbkOnTopOfScreen("MyWkb.xlsx")
End Sub