Upgrade from Excel 2013 to 2016 - VBA & Data Model Problems

[cnewell7]

Yesterday I upgraded from Office 2013 to 2016 via an Office 365 subscription at work

Prior to this I had developed an application in Excel with several workbook (power) queries integrated within. Refreshing all of these took about 10 minutes as they are pulling in quite large data files

Following the upgrade to 2016, I opened my workbook in Excel 2016 and ran the same procedure and it took 45 minutes

The other disturbing things I noticed were the following:

The PowerPivot window takes an eternity to load up

When finally opening the Data Model window Excel lurches into a state of trying to refresh all of the queries I have set up. I try to press escape to get out of this to no avail and have to wait for all of them to finish 'processing'

I can also see that in the data model window itself is struggling to actually show the numbers / data I have in there and takes significantly longer than in 2013. This seems symptomatic of a memory type issues and sometimes it hangs and goes into 'not responding' never to return at all

I have seen 'exception errors' thrown up and also a message to indicate that PowerQueries were built in a different version. I can also see a message I haven't seen before in the taskbar when I open my workbook : 'loading .net framework'

Suffice to say I cannot continue to use the Power BI features in Excel 2016 with these current issues and have had to rollback to 2013. I'm keen to get to the bottom of why I'm suffering a marked degradation in performance. If anything I was expecting performance to be better given that Power Query and Power Pivot are fully integrated into Excel

I have a Portege i5 with 16gb of RAM on 64 bit Office so don't think my hardware is at fault

Prior to upgrading Office I had the Power Query and MS Access DB engine addins installed and did not remove them prior to upgrade. I also have Microsoft .NET Framework 4.5.2

Would really be grateful for guidance as I'm an early adopter for Office 2016 and unfortunately need to approach the IT department with a solution as well as the problem!

[JasonDunbar]

Not really Power BI relevant, but my $0.02 - I had many performance issues when I did an upgrade from Office 2013 to 2016, both from O365. In the end I got fed up and completely removed any trace of Office applications before starting fresh with 2016.

Can't give any assurance as to whether or not it'll solve your issues though, sorry 🙂

J

[luisrh]

I would suggest you stick with 2013 for now.  I had the same issue.  One of the problems is that any Power Query add-in fixes or additions cannot be updated on 2016 since Power BI is part of Excel in 2016.    MSFT seems to be updating Power Query add-in - those changes are simpler/ happen faster than updates to the full Excel app.

If you are doing content packs as well,  I suggest you stick with 2013.