I have a workbook that opens another workbook and prints data from the second workbook. The following is from the section of code from the first workbook that opens the second workbook (code that is irrelevant to the question has been modified or removed):

Application.ScreenUpdating = False Application.DisplayAlerts = False Dim wb_original As Workbook Dim ws_original As Worksheet Set wb_original = Application.ActiveWorkbook Set ws_original = Application.ActiveSheet Set opened_workbook = Workbooks.Open(filename) wb_original.Activate ws_original.Activate Application.DisplayAlerts = True Application.ScreenUpdating = True

This code was written for Excel 2010 and works as intended in that version of Excel. In Excel 2016, it does not work as intended.

In Excel 2010, after the code has completed, the worksheet in the first workbook from which the opening of the second workbook is triggered is displayed. By contrast, in Excel 2016, it is the opened workbook that is displayed after the code has completed.

Looking through Excel 2016 Help (!!!), I see mention of a difference between Excel 2016 and Excel 2010 regarding Single Document Interface and Multiple Document Interface that may be relevant, although I have not grasped the precise details.

I’m looking for advice from those familiar with Excel 2016.

I’ll repost the code:

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb_original As Workbook
Dim ws_original As Worksheet
Set wb_original = Application.ActiveWorkbook
Set ws_original = Application.ActiveSheet
Set opened_workbook = Workbooks.Open(filename)
wb_original.Activate
ws_original.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True

I am unable to test in Excel 2016 and I don’t have a requirement to do exactly what you need but I have a memory of doing similar type of things. In one example I could find, I create a new workbook from a sheet in the current one and then return to the current one when it is done. Cutting out all the other data, I have:

thisBook = ActiveWorkbook.Name
‘…Other stuff…
Windows(thisBook).Activate

I don’t return to the original sheet as it has been moved to a new workbook but that can be achieved, similarly e.g.

thisBook = ActiveWorkbook.Name
thisSheet = ActiveSheet.Name
‘…etc…
Windows(thisBook).Activate
Sheets(thisSheet).Activate

furious said:

I am unable to test in Excel 2016

At home I use Excel 2016 under Windows 7, whereas at work I have been recently upgraded to Excel 2016 under Windows 10.

furious said:

Windows(thisBook).Activate

I have always avoided using the Windows object. My investigation seems to indicate that this would be affected by the upgrade to Excel 2016.

KJW said:

At home I use Excel 2016 under Windows 7

Correction:

At home I use Excel 2010 under Windows 7

• I have always avoided using the Windows object

Why is that? I probably do a lot of things that should be avoided, mostly becuase I don’t know that they should be avoided…

I still can’t replicate in Excel 2016 because I have W7 and Excel 2010 at work but I can split the difference as I also have access to a W10/Excel 2013 combination.

I created your code in 2010 and it worked as intended. I did the same with mine and it also worked the same way.

Opening the same Excel file with the two codes on the W10/2013 system and yours didn’t work in the same manner as you describing it not working above. I ran mine and it worked as intended.

However, for brevity, in mine I did not include:

Application.ScreenUpdating = False/True
Application.DisplayAlerts = False/True

So I removed them from yours and then it worked as intended…

No time right now, but I’ll try and have a look with Excel 2016/Windows 10 later today.

KJW, what happens if you call the sheet directly at the end of the code? Example:

Workbooks(“your_original_workbook.xlsx”).Activate

Sheets(“your_original_worksheet”).Select

Or even make the above two lines a sub and call that sub at the end of the code?

There are quite a few little variances between 2007 office and te newer versions, but I wasn’t aware of too many changes in 2016.

furious said:

I also have access to a W10/Excel 2013 combination.

I have access to Excel 2013 under Windows 10 at work, but not on my workstation. I don’t know how Excel 2013 behaves running my code, though it is not clear if there is any benefit to finding out (apart from knowledge for its own sake).

• I don’t know how Excel 2013 behaves running my code, though it is not clear if there is any benefit to finding out (apart from knowledge for its own sake).

Okay, sorry I bothered…

For what its worth this:

• difference between Excel 2016 and Excel 2010 regarding Single Document Interface and Multiple Document Interface that may be relevant

appears to have happened when they went to 2013 and it is probably the cause of your issue…

furious said:

Okay, sorry I bothered…

You’ll go far on this forum. Godspeed!

furious said:

• I don’t know how Excel 2013 behaves running my code, though it is not clear if there is any benefit to finding out (apart from knowledge for its own sake).

Okay, sorry I bothered…

What I meant was that because my code needs to run on Excel 2016 and not on Excel 2013, how it runs on Excel 2013 is immaterial to my needs.

KJW said:

furious said:

• I don’t know how Excel 2013 behaves running my code, though it is not clear if there is any benefit to finding out (apart from knowledge for its own sake).

Okay, sorry I bothered…

What I meant was that because my code needs to run on Excel 2016 and not on Excel 2013, how it runs on Excel 2013 is immaterial to my needs.

… and testing the code in an Excel 2013 machine at work is probably more effort than it’s worth.

• and testing the code in an Excel 2013 machine at work is probably more effort than it’s worth.

That’s how I feel…

furious said:

However, for brevity, in mine I did not include:

Application.ScreenUpdating = False/True
Application.DisplayAlerts = False/True

So I removed them from yours and then it worked as intended…

I just tried this in 2016 and it works also, i.e.

with:
Application.ScreenUpdating = False/True
Application.DisplayAlerts = False/True

it doesn’t return to the first file.

with:
Application.ScreenUpdating = False and Application.DisplayAlerts = False commented out

It does return to the first file.

I tried with the =False statements reactivated, and the =True statements moved to before the wb_original.Activate, ws_original.Activate, but that didn’t work.

The Rev Dodgson said:

furious said:

However, for brevity, in mine I did not include:

Application.ScreenUpdating = False/True
Application.DisplayAlerts = False/True

So I removed them from yours and then it worked as intended…

I just tried this in 2016 and it works also, i.e.

with:
Application.ScreenUpdating = False/True
Application.DisplayAlerts = False/True

it doesn’t return to the first file.

with:
Application.ScreenUpdating = False and Application.DisplayAlerts = False commented out

It does return to the first file.

I tried with the =False statements reactivated, and the =True statements moved to before the wb_original.Activate, ws_original.Activate, but that didn’t work.

Unfortunately, I didn’t get the opportunity at work since you wrote this to try this… maybe next week.

• Unfortunately, I didn’t get the opportunity at work since you wrote this to try this… maybe next week.

Have you been at work since I suggested the exact same thing earlier?

furious said:

• Unfortunately, I didn’t get the opportunity at work since you wrote this to try this… maybe next week.

Have you been at work since I suggested the exact same thing earlier?

That was the same day… so no.

Application.ScreenUpdating

It will be an angry moment if I’m forced to see, even briefly, the workbook that is opened. That is, I don’t consider removing Application.ScreenUpdating from my code to be an acceptable solution.

On a side note, while on the topic of Excel 2016, one other thing that has made me feel dismay is the absence of offline Help. For Excel 2010, even with the choice between online and offline Help ^†, I chose the offline Help because I felt it was much better.

^† Apparently, the link to online Help for Excel 2010 is currently broken.

KJW said:

On a side note, while on the topic of Excel 2016, one other thing that has made me feel dismay is the absence of offline Help. For Excel 2010, even with the choice between online and offline Help ^†, I chose the offline Help because I felt it was much better.

^† Apparently, the link to online Help for Excel 2010 is currently broken.

I totally agree. Not only is the on-line help much slower to open, it has been completely re-written and is now much worse than it was.

On the other hand your objection to the second worksheet flashing briefly on the screen seems just a little bit picky :)

The Rev Dodgson said:

On the other hand your objection to the second worksheet flashing briefly on the screen seems just a little bit picky :)

In itself, the brief flashing on the screen is not much of a problem. But considering that I may have to replace code that is both logically correct and works in Excel 2010 with code that is both logically flawed and doesn’t quite work in Excel 2016, I feel justified in being peeved. Why are we even buying Excel 2016? Is it that we are just being bullied by M$? They tell us that they are improving our experience with their product, but I’m not seeing the improvement. :-(

KJW said:

In itself, the brief flashing on the screen is not much of a problem.

I eventually got the opportunity at work to try commenting out the Application.ScreenUpdating lines in the actual real-world code. To my surprise, the result wasn’t as bad as I had expected. The opened workbook wasn’t seen but instead was the brief flashing of a blank window. I’ve noticed that Excel 2016 displays such a blank window while the VBA code is busy running.

KJW said:

In itself, the brief flashing on the screen is not much of a problem.

I eventually got the opportunity at work to try commenting out the Application.ScreenUpdating lines in the actual real-world code. To my surprise, the result wasn’t as bad as I had expected. The opened workbook wasn’t seen but instead was the brief flashing of a blank window. I’ve noticed that Excel 2016 displays such a blank window while the VBA code is busy running.