AppActivate works in Excel 2007 but not in 2010

Question

I have a excel macro that I run in Excel 2007 and it opens a mainframe application so I can import data from my spreadsheet automatically.

This has been working fine, however it doesn't work in Excel 2010.

I have tried using the shell command to use the ID but the other application is a mainframe application and non windows based.

However,

AppActivate "Title" (to open the mainframe application) was working fine in excel 2007.

In Excel 2010 I am getting a Run-time error 5 - Invalid procedure call or argument.

I've been trying to solve this for two days and it all works fine on 2007 version.

Any help would be really appreciated.

Dim appName as String 

appName = Range("AppName").Value 'this is a name stored in my excel spreadsheet for the mainframe app 

AppActivate (appName) => this line is giving runtime error '5' invalid procedure call or argument

Answer 1

Thanks for the answers, I only found out later that my users were launching the Excel 2016 version from a remote location, so the application they were trying to open could obviously not be found. The previous version of Excel was launched from their desktop so it worked.

In a nutshell, the AppActivate function works fine for both Excel version.

Thanks for your time.

Regards

Answer 2

I found this code, hope it helps:

Dim Myself as string
Myself = activewindow.caption

Then AppActivate(Myself) would take focus back to the original spreadsheet.

After the "upgrade", however, the AppActivate line started giving me errors, and I finally figured out that if I only had a single open spreadsheet, the title in the Windows Task Bar was just "Microsoft Excel".

I made a temporary fix by changing to

Myself = "Microsoft Excel - " & activewindow.caption

https://www.mrexcel.com/forum/excel-questions/566273-appactivate-excel-2010-a.html

Answer 3

If you want to return the focus back to the Excel where your VBC code lies, aka ThisWorkbook object, then you can use the following line:

AppActivate Title:=ThisWorkbook.Application.Caption

Answer 4

This error comes when the AppActivate doesn't get exact title. You can try below code and see if it helps you.

    Public Sub AppActTest()
    Dim objWd As Object
    Dim objTsk As Object
    Dim blOpenedByCode As Boolean

    On Error Resume Next
    Set objWd = GetObject(, "Word.Application")
    If objWd Is Nothing Then
        Set objWd = CreateObject("Word.Application")
        blOpenedByCode = True
    End If
    On Error GoTo 0

    For Each objTsk In objWd.Tasks
        If InStr(objTsk.Name, "MainframeApplicationName") > 0 Then
            objTsk.Activate
            objTsk.WindowState = wdWindowStateMaximize
            Exit For
        End If
    Next

    If blOpenedByCode Then objWd.Quit

    Set objTsk = Nothing
    Set objWd = Nothing

    End Sub

It will require Microsoft Word to be installed on your computer. It will work with partial match.

Answer 5

I use this macro to open Bookmarks from Excel 2010 in Firefox.

It has worked - then sometimes it doesn't work (Run-time error 5)

I see the fix as: close & re-open Firefox, then try it - works

What gets messed up, so it doesn't work?

Sub Open_a_Bookmark(control As IRibbonControl)

' Open a Bookmark in Firefox . . . Firefox has to be open for this to work

' Go to the Row of the bookmark you want, then click this button.
' It automatically goes to the URL column, and copies it.
    Cells(ActiveCell.Row, "BK").Activate
    ActiveCell.copy

' Open a new tab in Firefox with Ctrl+T
    AppActivate "Firefox"
    SendKeys ("^t"), True

' Sometimes you have to click this macro again, to get it to work, because the "paste" doesn't get to Firefox.
' Give it a second before pasting
    Application.Wait (Now + TimeValue("00:00:01"))

' The focus defaults to the Address Bar.  Paste the URL / Enter
    SendKeys ("^v~"), True

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

' See the bottom of "Process_Bookmarks" for details.  Used at the end of macros, when necessary.

    SendKeys "{NUMLOCK}", True

    Application.Wait (Now + TimeValue("00:00:01"))
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
End Sub

Answer 6

I had some issues with one computer at my work, where the code was trying to force the activation of one specific Excel Window. I found what could be a potential cause of failure of AppActivate.

• Let's say that your excel file is named "ExcelTable.xlsx".

It appears that the Excel window caption will change depending on if you told Windows Explorer to "hide" known file extensions.

So if extensions are hidden, the Excel caption shown in the title bar will be "ExcelTable"

But if the known extensions are NOT hidden in Windows Explorer, the Excel caption shown in the title bar will be "ExcelTable.xlsx"

On the other hand, the Excel function that is used in my script, "ActiveWorkbook.Windows(1).Caption" is ALWAYS returning the extension, even when Windows Explorer is hiding known files extensions.

So we get an error.

Note that the real window title caption, in mt case, is also showing " - Excel" appended to the end, but this part seems to be ignored by "AppActivate" so only the filename is relevant in my case. This could be different on your side.