Excel file locked for editing dynamic file sharing

#Excel file locked for editing dynamic file sharing how to#
#Excel file locked for editing dynamic file sharing update#

In the Project Explorer Window (Ctrl + R if it's not showing) select the sheet which contains the Power Query table.

Hide the worksheet and open the Visual Basic Editor (press Alt+F11).I am not VBA guy, but I remembered one tip from Mynda TreacyвЂ™s dashboard course which I applied here. I spent time Googling for solution to this but could not find one рџ™‚ Our Solution In fact, query refreshes also fail if we try to refresh data manually.

Of course, Excel protects the whole workbook structure using this method, which causes Power Update to fail. The end result is that our Customers table is hidden and cannot be unhidden and everything looks promising. Second issue, in order to protect Power Query table, we need to hide these sheets and protect the workbook. If you only need your Power Queries to update each time the Excel workbook is opened, however, you could force an update by changing the table's connection properties to force an update upon open. Note from Ken: I haven't seen Slobodan's model, so there may be a need to use Power Update to do what he's doing. At the same time, we would like to protect Excel tables sourced via Power Query from careless usersДЇor the first point, we have Power Update - a tool which allows you to schedule daily refresh.We want Power Query to be scheduled for automatic refresh on a daily basis.

How to make it fully automated? We have two goals here: We then use our Power Query table вЂњCustomersвЂќ as the source for dropdown lists in calculation model. We have scheduled daily export of all our customers from SAP to a file on a network drive, and use this file as the data source for a local PQ table in the workbook. This is where Power Query comes to the rescue. In other words, whenever a new customer is created in SAP, they should be able to select this customer in Excel using a dropdown list. The idea is to make these dropdown lists dynamic. Inside this Excel file, they have a lots of drop down lists from which they can choose customer, partner etc.

We created calculation model for our sales people (Full cost calculation). Thank you Ken for this opportunity! Glad to make some kind of contribution, to all of you PQ users. Recently, my team and I had faced a problem with refreshing PQ tables that we managed to solve with a simple trick (no VBA coding), and shared it with Ken who asked me to share it with community. I thought it would be cool if he shared it with everyone, so asked him to write up a little blog post on it, and here it is! Take it away SlobodanвЂ¦ The data validation lists are sourced from tables loaded via Power Query, and leverage a little hack to hide them from prying users eyes.

Slobodan emailed me to describe a trick to protect Excel tables that he is using to drive data validation lists.

#Excel file locked for editing dynamic file sharing update#

#Excel file locked for editing dynamic file sharing how to#