AppActivate

[gmooney]

Can anyone tell me if certain versions of Excel won't handle this function?

AppActivate "Category Review Links.xlsm"

It works fine on my PC (Microsoft 365 Apps for Enterprise 64bit) when running this code but I have a user with Microsoft ProPlus 32bit that it errors out on.

 

[Eric W]

I'm not aware of any versions that won't handle it. But some versions have different names for the apps. Your file might be named "Excel - Category Review Links.xlsm" instead, for example. You can try this code that searches through all the open windows to see if any of them have a partial match for your app name:

Option Explicit

' These are API calls to get a list of active windows
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Private Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Boolean

Private Const GW_HWNDNEXT = 2

Sub Testit()
Dim lhWndP As Long, AppName As String
    
    AppName = GetNameFromPartialCaption(lhWndP, "Category Review Links")
    If AppName <> "" Then
        AppActivate (AppName)
    End If
    
End Sub

Private Function GetNameFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As String
Dim lhWndP As Long
Dim sStr As String

    GetNameFromPartialCaption = ""                                      ' Default is empty
    lhWndP = FindWindow(vbNullString, vbNullString)                     ' Get the Parent Window handle
    Do While lhWndP <> 0                                                ' While there are more handles . . .
        sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))         ' Get the window name for that handle
        GetWindowText lhWndP, sStr, Len(sStr)                           ' Get the window name for that handle
        sStr = Left$(sStr, Len(sStr) - 1)                               ' Drop last character
        If InStr(sStr, sCaption) > 0 Then                               ' Does it contain our string?
            GetNameFromPartialCaption = sStr                            ' Yes?  then save the name
            lWnd = lhWndP                                               '            save the handle
            Exit Do
        End If
        lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)                         ' No? then get the next window
    Loop

End Function

 

[gmooney]

I'm not aware of any versions that won't handle it. But some versions have different names for the apps. Your file might be named "Excel - Category Review Links.xlsm" instead, for example. You can try this code that searches through all the open windows to see if any of them have a partial match for your app name:

Option Explicit

' These are API calls to get a list of active windows
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Private Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Boolean

Private Const GW_HWNDNEXT = 2

Sub Testit()
Dim lhWndP As Long, AppName As String
   
    AppName = GetNameFromPartialCaption(lhWndP, "Category Review Links")
    If AppName <> "" Then
        AppActivate (AppName)
    End If
   
End Sub

Private Function GetNameFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As String
Dim lhWndP As Long
Dim sStr As String

    GetNameFromPartialCaption = ""                                      ' Default is empty
    lhWndP = FindWindow(vbNullString, vbNullString)                     ' Get the Parent Window handle
    Do While lhWndP <> 0                                                ' While there are more handles . . .
        sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))         ' Get the window name for that handle
        GetWindowText lhWndP, sStr, Len(sStr)                           ' Get the window name for that handle
        sStr = Left$(sStr, Len(sStr) - 1)                               ' Drop last character
        If InStr(sStr, sCaption) > 0 Then                               ' Does it contain our string?
            GetNameFromPartialCaption = sStr                            ' Yes?  then save the name
            lWnd = lhWndP                                               '            save the handle
            Exit Do
        End If
        lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)                         ' No? then get the next window
    Loop

End Function

Hi Eric,

I ran your code and nothing appeared to happen? Should I have expected some result to display?

 

[Eric W]

If nothing happened, then likely it couldn't find a match at all for your window name. Try putting

Debug.Print sStr

right after the sStr = Left . . . line, and it will give a list of open windows. See if any of them are close to the one you expect to find.

 

[gmooney]

If nothing happened, then likely it couldn't find a match at all for your window name. Try putting

Debug.Print sStr

right after the sStr = Left . . . line, and it will give a list of open windows. See if any of them are close to the one you expect to find.

Eric,

So I added that line of code. I then went to your code and changed the AppName = GetNameFromPartialCaption(lhWndP, "Category Review Links") line TO INCLUDE the name of my company to the actual file name "Category Review xxxxxxx Links". The immediate window did not find anything. But when i took the company name out of your code and went back to "Category Review Links" and also saved my Excel file back to "Category Review Links" the immediate window found this at the very end.

Align
Align
Align
View
Run
Run
Run
Run Sub/UserForm (F5)
Microsoft Visual Basic for Applications - Category Review Links.xlsm [running] - [Module1 (Code)]

 

[Eric W]

So it looks like the last line might be the window you want? Did you try using

AppName = GetNameFromPartialCaption(lhWndP, "Category Review")