Word.GetAddress in Excel / The "Check Names" dialog displays in background

Question

I am using the Word.GetAddress function in an Excel document to retrieve the first & last names of someone if he is in the GAL.

From what I have understand, the only way to have the built-in "Check Names" dialog is to use the Word.GetAddress function.

When the name entered matches more than entry, the "Check Names" displays but in the background. I have to Alt+Tab to get it.

I have tried to use the "Activate" function or the "WindowsState" property to bring it upfront but I am stuck ...

Function getFirstAndLastNames(pName As String) As String
    Dim oWord As Word.Application
    Dim strAddress  As String

    On Error GoTo getFirstAndLastNames_Error

    'If the search doesn't work, returns the argument
    getFirstAndLastNames = pName

    'Create the Word object to use GetAddress
    Set oWord = CreateObject("Word.Application")

    'Search
    strAddress = oWord.GetAddress(Name:=pName, CheckNamesDialog:=True, AddressProperties:="<PR_GIVEN_NAME> <PR_SURNAME>")

    'If there is a result, the function returns it
    If strAddress <> "" Then getFirstAndLastNames = strAddress

    'Quit Word
    oWord.Quit

    Set oWord = Nothing

    On Error GoTo 0
    Exit Function

getFirstAndLastNames_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getFirstAndLastNames of Module Test"

    If Not (oWord Is Nothing) Then oWord.Quit

End Function

I have seen this post where there was a similar issue resolved but it doesn't say how ...

Thanks in advance for your help.

score 1 · Accepted Answer · edited May 23 '17 at 12:12

The edit to the other post does not state that they found a resolution for the box not coming to the front; only that it could be made visible using alt-tab to bring it to the front.

You have a deadlock in that your Excel code is stopped, waiting for Word and you need an action to have the Word (or rather Outlook) window brought to the front so the user can find it.

You could minimize and restore the Excel window but it is kludge and if there are other windows on screen then it'll be unreliable as the dialog you need will be hidden behind those too.

What you need to do is a bit ugly but will work. Which is to have a helper script or application which you can fire off asynchronously using Application.Run which will start the app and continue to execute in VBA. That script/app will wait for a little while (to give VBA time to run the GetAddress line) and then bring that dialog to the front using the windows API.

Most scripting or programming languages will be good enough and which one you choose depends on what you are most comfortable with. StackOverflow has an example for Powershell that you can adjust to your needs.

Hello James. Unfortunately I don't know PowerShell at all but I am afraid that this would bring to front any Word document that are open on the PC. — abillon, Sep 01 '14 at 08:35

score 0 · Answer 2 · answered Sep 01 '14 at 08:46

Finally, I found an article on the support of Microsoft.com that explain how to use CheckSpelling outside of Word. I adapted it to my use.

The code position the Word window off the screen but the dialogs appears in the foreground.

Function getFirstAndLastNames(pName As String) As String

    Dim oWord As Word.Application
    Dim strAddress  As String
    Dim lOrigTop As Long
    Dim lOrigState As Byte

    'Display the "Check names" dialog (available only with Word.Application ...)

    On Error GoTo getFirstAndLastNames_Error

    'If the search doesn't work, returns the argument
    getFirstAndLastNames = pName

    'Create the Word object to use GetAddress
    Set oWord = CreateObject("Word.Application")

    'Position Word off screen to avoid having document visible
    'http://support.microsoft.com/kb/243844/en-us
    lOrigTop = oWord.Top
    lOrigState = oWord.WindowState
    oWord.Top = -3000
    oWord.Visible = True
    oWord.WindowState = wdWindowStateMinimize
    oWord.Activate


    'Search
    strAddress = oWord.GetAddress(Name:=pName, CheckNamesDialog:=True, AddressProperties:="<PR_GIVEN_NAME> <PR_SURNAME>")

    'If there is a result, the function returns it
    If strAddress <> "" Then getFirstAndLastNames = strAddress

    'Reset the position and state of Word and quit the application
    oWord.Visible = False
    oWord.Top = lOrigTop
    oWord.WindowState = lOrigState
    oWord.Quit

    Set oWord = Nothing

    On Error GoTo 0
    Exit Function

getFirstAndLastNames_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getFirstAndLastNames of Module Test"

    'If an error raised, Reset the position and state of Word and quit the application
    If Not (oWord Is Nothing) Then
        oWord.Top = lOrigTop
        oWord.WindowState = lOrigState
        oWord.Quit
    End If

End Function

Word.GetAddress in Excel / The "Check Names" dialog displays in background

2 Answers2