Restoring focus to Excel programmatically on a MAC

Neil Fleming
Neil Fleming used Ask the Experts™
on
Malfunctioning AppActivate in Excel VBA 2016 for Mac is driving me nuts.

Anyone found a way to return to the Excel app having switched focus to, say Word, programmatically?

This triggers an error (works fine on a PC)
AppActivate ThisWorkbook.Windows(1).Caption

Open in new window


This triggers no error on a Mac but also has zero effect. (On a PC it causes an error, but that would be fine using conditional compilation).
AppActivate "Microsoft Excel"

Open in new window


I have experimented with simply minimising the Word window, but cannot then re-maximise it programmatically on a Mac either.

This works
WordApp.ActiveWindow.WindowState = wdWindowStateMinimize

Open in new window


But this does not:

WordApp.ActiveWindow.WindowState = wdWindowStateNormal

Open in new window


I seem only to be able to restore Word manually once I've minimised it.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Chip Pearson has some code here that uses APis instead of AppActivate. I've never used it.
Neil FlemingConsultant and developer

Author

Commented:
hmm Malwarebytes seems to think the Pearson site has a trojan..
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've attached a workbook that contains the code from the site, and here's the text.
2019-08-10_11-01-39.png29154611.xlsm
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Neil FlemingConsultant and developer

Author

Commented:
I accessed it anyway.. Trouble is, windows API calls will not work on a Mac..
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Of course, sorry.
Neil FlemingConsultant and developer

Author

Commented:
They seem to have thoroughly broken the "interoperability" in all kinds of ways.. not sure if it is Apple or MS who is to blame.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What happens if you just do AppActivate "Excel"?
Neil FlemingConsultant and developer

Author

Commented:
"invalid procedure call or argument".. sadly
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Investigate the Shell function. Other than that I'm out of ideas.
Neil FlemingConsultant and developer

Author

Commented:
Thank you.. I did play with Shell earlier, but I think there's a problem with Excel calling itself.. It didn't seem to like it much. But I may try it again. Infuriating, really. It is dead easy to activate Word, and seemingly impossible to get back to Excel...
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you have control over the Word doc? If so put the go-back-toExcel code there.

Another solution which is probably impractical is to use Parallels Desktop (or similar program) to create a Windows virtual machine on your mac and run Excel 2010 or Excel 365, etcetera in that VM. That's what I do.
Neil FlemingConsultant and developer

Author

Commented:
The Word document can be any Word document, so the code cannot be embedded in it. I am writing a tool for multiple users, and it's not practical to create a VM on all their machines.

Weirdly, while Word VBA has an "application.activate" command, which foregrounds it with no problem, there is no equivalent command in Excel.  The "Activate" command is present for workbooks and worksheets,  of course, but does not foreground the Excel application itself.

Otherwise I could probably call Excel from a "WordApp" object created in the Excel vba.

Annoyingly, Excel also features the handy "ActivateMicrosoftApp" command these days, but it doesn't seem able to activate itself..

I found an interesting way to wake up Excel from Word, though, but (of course) it won't work on a Mac, because the OS sandboxes apps so they cannot use DDE.

But if anyone seeks an alternative in Window to AppActivate, this works:

Sub WakeUpExcel()

Dim xlApp As Excel.Application
Dim iChannel As Long

On Error Resume Next
Err.Clear 'get ExcelSet xlApp = GetObject(, "Excel Application")
If Err.Number = 429 Then
Set xlApp = CreateObject("Excel Application")
End If
DoEvents
 
'create a DDE chanel
iChannel = DDEInitiate(App:="Excel", Topic:="System")
'open a new workbook using DDE
DDEExecute Channel:=iChannel, Command:="[New(1)]"
'immediately close it
DDEExecute Channel:=iChannel, Command:="[Close]"
DDETerminate Channel:=iChannel
'focus remains with Excel...>>
End Sub

Open in new window


..but meanwhile, I am still stuck finding a way to do it with  the Mac OS

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial