How to manage instances of Excel 2016 in VBA (Excel 2010 to 2016 problem)

[SillyWilly64]

Hi everyone, frequent reader, first time poster.

I have a question similar to that posted by user DougRobertson on March 30th.

I have also noticed that Excel 2016 seems to open multiple instances compared with Excel 2010. So here is the problem I need to solve. My code opens another workbook, makes a change to it and then saves and closes it. But, the flag "Application.DisplayAlerts = False" no longer works, so I get the usual unwanted prompt questions. Apparently VBA can't figure out which instance I'm referring to. So how do I open another workbook and get the correct "handle" to it so that the alerts will work?

Here is the code that works fine in 2010 but does not work in 2016

Workbooks.Open Filename:=file
wbkname = Right(file, Len(file) - InStrRev(file, "/"))
Set sourceSheet = Workbooks(wbkname).Worksheets(worksheet_tab)
sourceSheet.Activate
-- Do some Stuff --
Application.DisplayAlerts = False                   '<-----  THIS LINE NO LONGER WORKS
ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = True

 

[Norie]

Are you sure there are multiple instances of Excel open?

Is it not that each open workbook in the open instance of Excel has it's own tile on the taskbar so it appears there are multiple instances.

 

[SillyWilly64]

"Multiple Instances" is my interpretation because I see two windows on the task bar and VBA will not set the alert properly.

The main thing is that I want the alert to flag to function properly.

 

[RoryA]

What prompt are you actually seeing?

 

[SillyWilly64]

It brings up the usual "Save As" dialog box.

In prior the prior version of Excel it just skips all of that and saves the changes back to the original file in the original location without the prompt.

If I step through the code, I can see that the Application.DisplayAlerts = False is not working. The value remains "True" in the immediate window before and after the line of code that sets it to "False".

 

[RoryA]

I'd guess the wrong workbook is deemed the active one. No previously saved workbook would bring up that dialog when you save it.

 

[SillyWilly64]

Exactly! Which goes back to the original question of what method or command will allow me exactly specify which workbook I am referring to. Keep in mind that the correct workbook is already active. Note also that when I tell excel to close the workbook it closes the right one. How then to get the Application.DisplayAlerts flag pointed at the right workbook?