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

Please share your code attempt, and point us to the line you are receiving this error. — Shai Rado, Oct 25 '17 at 12:12
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 — RShome, Oct 25 '17 at 12:24
Instead of placing code in your comments, you may [edit your original question](https://stackoverflow.com/posts/46932069/edit) to make it much easier to read. — K.Dᴀᴠɪs, Oct 25 '17 at 12:59
@RShome have you tested any of the answers you got ? any feedback? — Shai Rado, Oct 29 '17 at 06:33

score 2 · Answer 1 · answered Oct 25 '17 at 12:20

2

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

answered Oct 25 '17 at 12:20

Shai Rado

33,032
6
29
51

score 1 · Answer 2 · answered Oct 25 '17 at 12:16

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

Thanks all, sorry maybe I didn't make myself clear. I actually need to open the mainframe application so it can accept inputs from my excel spreadsheet. — RShome, Oct 25 '17 at 12:22

score 0 · Answer 3 · answered Oct 25 '17 at 13:02

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.

I'll try this code thanks very much. My existing code does work with Excel 2007, just not in 2010, so in 2007 it is obviously matching the application title — RShome, Oct 25 '17 at 13:27

score 0 · Accepted Answer · answered Oct 30 '17 at 10:05

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

score -2 · Answer 5 · edited Feb 19 '19 at 20:36

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

AppActivate works in Excel 2007 but not in 2010

5 Answers5

Linked