Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts

    I want Excel always on top

    I call Excel from within an Access Application with the following (pretty standard) code:
    Dim objXL As Object

    On Error GoTo PROC_ERROR
    Set objXL = GetObject(, "Excel.Application")
    objXL.Visible = True

    Dim strWkbFullName As String
    strWkbFullName = CurrentProject.Path & "\" & colParameters("ExcelWorkbook")

    Dim objWkb As Object
    Set objWkb = objXL.Workbooks.Open(strWkbFullName)

    Dim objWks As Object
    Set objWks = objWkb.Worksheets(c_strPvtWks)
    objWks.Activate
    The Access application is excuted in two possible system environments: either Office 2007 & Vista (32-bits) or Office 2010 & Win7 (64-bits). The problem I have is that in the first environment Excel comes on top where as in the latter environment Excel remains hidden underneath Access.

    I would prefer a predictable behaviour where Excel always comes on top. Can this be forced?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,133
    Thanks
    39
    Thanked 211 Times in 198 Posts
    Just a quick guess, try:

    objWks.setfocus

    HTH,
    Maud

  4. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Setfocus in unfortunately not a worksheet method!

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,133
    Thanks
    39
    Thanked 211 Times in 198 Posts
    Sorry, my mind was thinking select; my fingers typed setfocus. Try adding the line at the end:
    objWks.select

  6. #5
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Adding Select doesn't put Excel on top in the Office 2010/Win7 environment. Sorry.

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    I don't know if UAC will interfere on Win 7 but try adding:
    Code:
    Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    to the top of your code module and then
    Code:
    Set objXL = GetObject(, "Excel.Application")
    objXL.Visible = True
    setforegroundwindow objxl.hwnd
    This assumes you are not running 64bit Office, only 64bit Windows.
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2013-04-05)

  9. #7
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    @rory: thanks for your reply. Unfortunately, it does not work.

    But let me briefly describe the pretty standard application. When access is opened it performs some actions (retrievals) on the underlying database and then produces a full screen form that acts as a switchboard. That form contains descriptive buttons, which; when clicked upon produce a standard access report. One such "report" however is an Excel worksheet.

    I put your code into the module code for the switchboard form; the Declare statement on top and the setforegroundwindow call inside the OnClick event for that button. The resulting behaviour (office 2010/win7) is as follows:

    1. The switchboard form appears but is no longer fullscreen displayed. Some, but not all buttons appear at a different location on the form
    2. When the "excel" button is clicked, I get following error message: The expression OnClick you entered as the event property setting produced the following error: Constants, fixed length strings, arrays, ueser defined types and Declare statements are not allowed as Public members of object modules.

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    1. I cannot see how simply adding that code could affect the display of your form!
    2. If you put the code in a form you need to use Private Declare Function
    Regards,
    Rory
    Microsoft MVP - Excel.

  11. #9
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks rory: that did it. After reading your answer, the error message was clear and rather explicit!

    PS: If ever I switch to office 64-bit, would it be sufficient to change "user32" for "user64"?

  12. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    No - you'd need conditional compilation and to declare PtrSafe and use a LongPtr for the window handle

    Code:
    #If VBA7 Then
    Declare PtrSafe Function SetForegroundWindow Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As LongPtr) As Long
    #Else
    Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    #End If
    Last edited by rory; 2013-04-08 at 06:00.
    Regards,
    Rory
    Microsoft MVP - Excel.

  13. #11
    Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    91
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    1. I cannot see how simply adding that code could affect the display of your form!
    It didn't but in an unexpected way: In fact, the form was displayed as it was laid out at design time. It happens to be a form where the controls are dynamically put in place when the form is opened. I suspect that the erroneous code line (without the Private declaration) which is the first line of code in the module, caused the module NOT to compile at all, thereby leaving the controls at their original positions. I coded that form that long time ago, so that it was not immediately clear what happened. Sorry for the confusion and thanks for your help
    Last edited by rvWoody; 2013-04-12 at 03:45. Reason: grammatical error

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •