Enable data query refresh (with PowerPivot tables) when workbook structure is protected
I want to protect the queries from being changed but want users to have the capability to refresh the data model (that uses PowerPivot).
Anonymous
shared this idea
1 comment
-
James Currie
commented
I design Excel applications with VBA.
Office 2010 was bad for performance, but Office 2013 is worse.
Please understand, these are not little 'Record Macro' apps, these are complex advanced applications with many thousands of lines of code, utilizing VBA forms, Web Services, XML, SQL Server access etc.
When Office 2013 came along, all of my financial apps slowed down by more than 30%. After trouble shooting, I discovered that the problem was in protecting a sheet. In the 2003 version, protect and unprotect were instantaneous, but in Office 2013, when I protect or unprotect in code, each page takes seconds to protect and unprotect a sheet.
I redesigned and reduced the number of times I protected and unprotected a sheet in code, but in many cases, this is unavoidable.
I increased performance dramatically by saving as a 2003 compatible XLS from XLSM. But this is not a good solution going forward.
This is pathetic. After all these years, your upgrades are ruining Excel, and making VBA an unacceptable choice for small local applications.
I work in a large Finance based organization, and I can tell you with certainty, VBA is a vital part of our work--and is used to automate many time-consuming local team tasks.
I also work with MS Access, and Access 2013 is abysmal. Another step down in capability and usability. I have downgraded to Access 2010 since 2013 is terrible.
I also design mid-tier C# Web Services, using SQL Server and Visual Studio. (still a great language and a still great database)
You at Microsoft have made changes to MS Office which have not been welcomed (did you really do a focus group? Not with my users!), and many users avoid using Office because it is now 'bloatware', and is cumbersome and complex.
What Microsoft DOES NOT REALIZE is that in large organizations, we have a tremendous network overhead, applications and security, which consume much of our available local machine RAM, which makes Office apps--with their superfluous 'animation' very, very slow.
I strongly suggest that Microsoft focus like a laser beam on VBA (forget .Net) because it is VBA which sets Microsoft apart from other spreadsheets.
Without VBA, any other spreadsheet would do the job.