Use a variable for an Application name

Question

I have a macro that will switch between programs. I've noticed that Excel 2010 and 2016 are named differently in the system (my semantics here are wrong, sorry!) and instead of having to change the code depending on which Excel, I was just thinking I could use a variable to set that.

Sub test()
'code here in Excel

Dim myApp
myApp = Application.Application
AppActivate "Google Chrome"
Call AppActivate("Google Chrome")
' do one or two things

AppActivate myApp
'do more things in Excel
End Sub

Unfortunately, the AppActivate myApp doesn't work. It throws

Run-time Error '5': Invalid procedure call or argument

Is there a way to do what I'm trying? I see on this site that I could do something like:

Public vPID As Variant
vPID = Shell("C:\Windows\system32\notepad.exe", vbNormalFocus)
AppActivate (vPID)

Except, what if Excel isn't located in the same file path on the two computers it's going to be used on?

Edit: It looks like I just need to set the Title of the Application to the variable (again, from that site):

Normally, the AppActivate statement is used to activate an existing application based on its title.

edit2: Getting closer, I found I could get the path to Excel.Exe by this, excelPath = Application.Path & "\Excel.exe" but can't figure out how to call that.

Not clear......do you want to **open** an instance of Word or **activate** an instance that you have already opened ?? — Gary's Student, May 03 '16 at 14:28
@Gary'sStudent - I want it to activate an instance that is already open. — BruceWayne, May 03 '16 at 14:32

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

6

To activate by the window title of the instance running the code:

AppActivate Application.Caption

Or by Process ID (likely more robust):

AppActivate pid

Where pid is the result of GetCurrentProcessId().

edited May 23 '17 at 12:16

Community

1
1

answered May 03 '16 at 14:25

Alex K.

171,639
30
264
288

Aha! Using the `.Caption` did the trick. I'll look into the `pid` part as well. Cheers! – BruceWayne May 03 '16 at 14:34

score 2 · Answer 2 · answered May 03 '16 at 14:46

2

Here is a very simple example of:

opening Word

activating it

Sub UsingWord()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
AppActivate wrdApp.Caption
End Sub

answered May 03 '16 at 14:46

Gary's Student

95,722
10
59
99

Use a variable for an Application name

2 Answers2