none
Can't bring Access WIndow to front when opened from WORD VBA RRS feed

  • Question

  • I'm having problem bringing an Access Window to the front when I open it from VBA in WORD.  It actually works fine when I run it in debug mode, step by step.  But when I just let it run on its own the Access Window never appears. 

    My stripped down code is:

    Private Declare Function FindWindow Lib "user32" Alias _
       "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
       As Any) As Long

    Private Declare Function BringWindowToTop Lib "user32" (ByVal _
       hwnd As Long) As Long

    Private Sub Document_Open()

        Dim b_Required as Boolean

        Dim l_WindowHandle As Long
        Dim l_ValueReturned As Long
       
        b_Required = True

        If b_Required Then

            'Open Mail-Merge Attachments.Mdb in same Folder as Mail-Merge DataSource
            With Access_Application
                .Visible = True
                .UserControl = True
                .OpenCurrentDataBase ( _
                    m_s_MailMergeDataSourceFolder & _
                    "\Mail-Merge Attachments.Mdb")
            End With

            'Use WindowHandle for "Mail-Merge Addresses" Window to Bring it to Top
            l_WindowHandle = FindWindow(vbEmpty, "On2it - Merge to E-mail with Addresses")
            l_ValueReturned = BringWindowToTop(l_WindowHandle)

        End If

    End Sub

    In DEBUG mode, by the time I get to the 'End With' the Access Window is on top - as I want it to be.  But when I run it without the debugger, the Access Window never appears.  It is open in the background but never seems to appear in front.

    So I looked it up in the Library; added and called FindWindow and BringWindowToTop - but they made no difference.  When I run them with the debugger, they both return a Zero. 

    Have I not specified the Window name correctly or something?  The Title Bar of my Access Window looks like this:

    Monday, June 8, 2015 12:32 PM

Answers

  • I do that a great deal of that in my Word macros, and find the most reliable way is to use the Win API SHELL method.  I'm not having any trouble with it with Word 2013 or Word 2010.  I seem to recall years ago before adopting that method encountering problems similar to your problem and like, you I had code to try to "find" the window.  Now, allI do is this. 

    Shell [path and name], vbMaximizedfocus

    Of course you require a Shell function declaration in the module as well.

    I should add that I design the code so that my userform has unloaded before the Shell statement runs. That might make a difference. I have found a number of file related things that don't work as before (Word 2003) if allowed to run from the userform code.

    Hope this helps. J.

    Wednesday, June 10, 2015 9:35 PM

All replies

  • Hi Brian,

    Based on your code, I did not see the definition of the "Access_Application", could you share us how did you define it? I made a test by defining it as Access.Application Object, and it worked correctly for me. I suggest you try the code below:

    Private Sub Document_Open()
        Dim b_Required As Boolean
        Dim l_WindowHandle As Long
         Dim l_ValueReturned As Long
         Dim Access_Application As Object
         Set Access_Application = CreateObject("Access.Application")
         b_Required = True
        If b_Required Then
            'Open Mail-Merge Attachments.Mdb in same Folder as Mail-Merge DataSource
             With Access_Application
                 .Visible = True
                 .UserControl = True
                 .OpenCurrentDatabase ("D:\OfficeDev\Access\Test.accdb")
             End With
        End If
    End Sub

    If you still have this issue, please feel free to let us know.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 9, 2015 11:49 AM
  • Hi Edward,

    Sorry I have defined Access_Application at the Module level exactly as you did with Late Binding. 

    It works great and does everything it should, leaving the Access Database open on top of Word - IF I debug it step by step.  If I just run it however, the Access Database opens OK, but it's behind Word.  I simply need to bring it to the front!

    Regards
    Brian

    Tuesday, June 9, 2015 12:58 PM
  • Hi Brian

    We still don't have enough context - we don't see at what point, and in what event handler, the Access application is being initialized. Or what else may be going on that could be conflicting with the execution.

    If you're using the Document_Open event then this code is executing from within the context of the document being opened. I'd assume, then, that the Access application is being initialized in a different context, outside the code container opening the database - not the case in Edward's test scenario. Do you get a different result if you test using everything within the same code context?


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 9, 2015 4:55 PM
    Moderator
  • Hi Cindy,

    I had originally declared ACCESS_Application as a Module Variable.  I have just tried declaring it locally within Document_Open.  It made no difference!  All the action takes place in Document_Open as you can see:

    Private Sub Document_Open()
    '
    '   Written by: Brian McGuigan
    '           of: On2it Software Ltd
    '      Version: 1
    '        Dated: 31-May-15
    'First Used in: NEXT Version
    '       Status: Being Tested
    '
    '      Purpose: This Subroutine is called when the Document first opens.
    '               It can therefore be used to perform any initialisation tasks required.

        Dim ACCESS_Application As Object        'This has been declared locally to TRY and get
                                                'the ACCESS Window to stay on top.
                                                'BUT it did NOT WORK!
                             
                                                'It DOES if I step through it with the Debugger,
                                                'but NOT if I just run it!
        
            Set ACCESS_Application = CreateObject("Access.Application") 'LATE Binding
            
            'Open Mail-Merge Attachments.Mdb in same Folder as Mail-Merge DataSource
            With Access_Application
                .Visible = True
                .UserControl = True
                .OpenCurrentDataBase ( _
                    m_s_MailMergeDataSourceFolder & _
                    "\Mail-Merge Attachments.Mdb")
            End With
             
    End Sub

    Wednesday, June 10, 2015 1:46 AM
  • Hi Brian

    FWIW this kind of problem (window opening behind another) crops up occasionally. I can't recall the reason ever being reliably isolated - it tends to be specific to individual configurations...

    Another thing you can try would be to build in "Sleep" loops to see if that gives Windows the missing time to display things differently. Or an "empty loop" (increment a counter x-times).

    Your comments use the term "mail merge": Is this Word's built-in mail merge or are you pulling data directly from the database? Is it absolutely necessary to open the database and show it to the user? If yes, why? If the user is to view a form or report perhaps using the Activate method of that form or report will bring the window to the front?

    You might also try putting .Visible and .UserControl after the OpenCurrentDatabase execution to see if that makes any difference.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, June 10, 2015 5:43 PM
    Moderator
  • I do that a great deal of that in my Word macros, and find the most reliable way is to use the Win API SHELL method.  I'm not having any trouble with it with Word 2013 or Word 2010.  I seem to recall years ago before adopting that method encountering problems similar to your problem and like, you I had code to try to "find" the window.  Now, allI do is this. 

    Shell [path and name], vbMaximizedfocus

    Of course you require a Shell function declaration in the module as well.

    I should add that I design the code so that my userform has unloaded before the Shell statement runs. That might make a difference. I have found a number of file related things that don't work as before (Word 2003) if allowed to run from the userform code.

    Hope this helps. J.

    Wednesday, June 10, 2015 9:35 PM
  • Hi Cindy,

    Thanks for your help.  I'll try your suggestions.  Even a 'DoEvents' might help.

    What I'm trying to bring to the front from Word VBA, is an Access Window displaying a single Form.  The purpose of the Database is to attach to the Word Document's Mail-Merge Data Source and display for the USER the E-mail Address, Name, Job Title and Organisation of each Recipient, so that they can use an Open Dialog to select Attachments to be sent all or specific Recipients of the Merge. 

    The Access Database is already going, as is the Mail-Merge with Attachments.  I set Outlook Offline so it can't send E-mails and perform a normal merge to E-mail, which of course has no Attachments.  Performing a normal Merge preserves the formatting of the E-mails.  I then go through the contents of Outlook's Outbox and add the required Attachments.   I then switch Outlook Online and let the E-mails get sent.   

    The alternative approach that merges to a separate document and then goes through the sections of the document, adding Attachments before sending them direct to Outlook as separate E-mails, works - but does not preserve their format, sending only plain text.  (I know.  I've tried it.)

    All I'm trying to do right now, is keep the wretched little Window on top, so that the Users can see it more easily.  I'll try your other suggestions too and report back.  With luck one of them will work.  :-)

    Regards
    Brian

    Thursday, June 11, 2015 12:22 AM
  • Hi Julie,

    Thanks for your suggestions.  I'll try Julie's first.  They are a tad easier to implement, but I think yours might be more likely to succeed.  I'll try them all and report back.

    If all else fails, then I can always display a message telling the User to look in the Task Bar.

    Regards
    Brian

    Thursday, June 11, 2015 12:31 AM
  • PRROGRESS!

    I tried:
        - calling .OpenCurrentDataBase first,
        - using DoEvents,
        - using Sleep for 5 seconds.
    none of which worked.

    However:
           Shell s_AccessPath & " """ & s_DatabasePath & """", vbMaximizedFocus
    did.  Which is great apart from the fact that I now have to read the Registry to find out the Access Path for any version of Access on anyone's PC.  (The good news is that we already know how to do this.) 

    So thanks for the suggestion Julie.

    Regards
    Brian


    Thursday, June 11, 2015 9:32 AM
  • Great :-)! THanks for taking a moment to drop by and confirm that Julie's approach worked.

    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 11, 2015 4:24 PM
    Moderator
  • Thank you, Julie, for supplying an important piece of this puzzle that has stumped so many developers over the years :-)

    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 11, 2015 4:25 PM
    Moderator
  • Glad to help.  You might try SHELLEXECUTE instead of SHELL.  It has been so long since I hacked it out that I cannot recall the reason I did not do so. It would have been my first choice because it doesn't require you to determine the .exe path.  I probably ran into the focus problem with that method. One of these days when I have some time to waste, I'll try it again.  J.
    Thursday, June 11, 2015 6:02 PM
  • Hi Julie,

    Well I did mess around with ShellExecute as well as Shell.  At the moment I have both in my code - and they both work.  I've discussed the merits of both below so that others can use the one that's best for them.

    I guess it's a question of 'horses for courses'.  If I was working in a situation where I was always sure where the executable file was, I would use Shell as it's a built-in Function and the ApplicationPath can be hard coded. 

    Shell Solution:

           Dim s_AccessPath as String
           Dim s_DatabasePath as String

           'Open Mail-Merge Attachments.Mdb in same Folder as the active document
           s_AccessPath = "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"  'or wherever
           s_DatabasePath = ActiveDocument.Path & "\Mail-Merge Attachments.Mdb"

           Shell s_AccessPath & " """ & s_DatabasePath & """", vbMaximizedFocus  'Note: use of single space
                                                                                                                        '         and double-quotes

    We are fortunate in that we have a library Function that we wrote some years ago, which returns the ApplicationPath. 

           s_AccessPath = ApplicationPath("Access")

    However writing ApplicationPath is not trivial, as it has to be able to read and search the Registry looking for all possible current and future version of the Application.

    ShellExecute Solution

    This is more robust for situations where you don't know where the Application has been installed and don't want to take on the task of reading the Registry to find out.   ShellExecute, however, needs a Handle for the ActiveWindow.  (Nor sure why it needs one - but it does.)  Fortunately there is a Windows API Function GetActiveWindow that does it for you and you need to declare ShellExecute itself.  So the ShellExecute solution looks like this:

    'Function Declarations linking to standard Windows Functions
    '------------------------------------------------------------
    Private Declare Function GetActiveWindow Lib "user32" () As Long

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                    (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                     ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

        Dim l_WindowHandle As Long
        Dim s_DatabasePath As String

        'Because Our Access Database is always in the same Folder as the Active Document  
        s_DatabasePath = ActiveDocument.Path & "\Mail-Merge Attachments.Mdb"

        l_WindowHandle = GetActiveWindow()

        'Open the Access database - leaving its Window on top
        ShellExecute l_WindowHandle, "open", s_DatabasePath, vbNullString, vbNullString, vbMaximizedFocus

    The only remaining problem was have is that currently vbMaximizedFocus does not appear to be doing its job.  I'm sure it was earlier.  Currently it's being displayed as a normal window.

    Regards
    Brian

    Saturday, June 13, 2015 12:41 AM