Vielleicht nutzt du Power Query genauso gerne und häufig wie ich und hast dir schon einmal die Frage gestellt: Wie kann ich denn meine Abfragen eigentlich schützen?
Denn manchmal möchte man einfach nicht, dass ein lieber Kollege oder ein neugieriger Anwender in deinen schönen Abfragen herumschnüffelt oder gar darin herumwurschtelt und deine ganze Arbeit zunichte macht. Aber wie kann man das verhindern?
Dieser Artikel zeigt, wie es geht. Und welche Kröte man dafür vielleicht auch schlucken muss.
Das Problem
Alles, was mit Power Query zu tun hat, befindet sich bekanntermaßen im Menü „Daten“ in der Werkzeuggruppe „Daten abrufen und transformieren“. Doch so sehr man auch die Menüs durchsucht, es gib hier leider nirgendwo einen Hinweis darauf, wie man seine Abfragen schützen kann. Weder in den Abfrageoptionen noch in den Datenquelleneinstellungen, auch nicht in den Eigenschaften zu den Abfragen.
Die für Power Query relevanten Werkzeuge
Und auch bei den Optionen für den Blattschutz, die man im Menü „Überprüfen“ aktivieren kann, findet man nichts zu Power Query.
Optionen für den Blattschutz helfen nicht weiter
Selbst wenn man hier alle Möglichkeiten deaktiviert und dann den Blattschutz aktiviert, lassen sich trotzdem Power Query-Abfragen weiterhin öffnen und verändern.
Kann man seine Abfragen also vielleicht gar nicht schützen?
Die Lösung
Doch! Und es ist sogar recht einfach, auch wenn man vielleicht nicht gleich darauf kommt. Denn wir waren gerade schon sehr nah dran. Man muss nämlich nur den Arbeitsmappenschutz aktivieren:
Menü „Überprüfen | Arbeitsmappe schützen“ und dann ein Passwort vergeben.
Die Lösung: Arbeitsmappe schützen
Noch ein Kennwort vergeben
Nachdem man das Passwort ein zweites Mal zur Bestätigung eingegeben hat, ist der Schutz aktiv und die Abfragen lassen sich nicht mehr öffnen und damit auch nicht einsehen oder verändern. Denn die entsprechenden Optionen sind jetzt nicht mehr auswählbar, wie ein Rechtsklick auf eine Abfrage zeigt:
Bearbeitungsfunktionen sind deaktiviert
Es ist nun auch nicht mehr möglich, neue Abfragen zu erstellen oder auch nur den Power Query-Editor zu öffnen:
Auch neue Abfragen sind nicht möglichDie Lösung: Arbeitsmappe schützen
Also eigentlich alles bestens, oder?
Wo Licht ist, gibt es auch Schatten
Leider hat die beschriebene Lösung einen Haken. Zumindest, wenn du nicht mit Microsoft 365/Office 365 arbeitest:
Bei meinen Tests mit Excel 2013 und dem Power Query-Add-In habe ich festgestellt, dass es bei aktiviertem Arbeitsmappenschutz nicht mehr möglich ist, die Abfragen zu aktualisieren. Die Abfragen laufen auf einen Fehler („Der Download wurde nicht abgeschlossen“) und neue oder geänderte Daten werden nicht mehr eingeladen:
Probleme in Excel 2013
Ob dieses Problem speziell an dem in Excel 2013 noch notwendigen Power Query-Add-In liegt, oder ob der Fehler auch in Excel 2016 und Excel 2019 auftritt, kann ich mangels Verfügbarkeit bei mir leider nicht testen. Vielleicht hat ja hier der eine oder andere Leser eine Information dazu und schreibt das unten in die Kommentare.
Mit Microsoft 365/Office 365 hingegen konnte ich keine Probleme feststellen, das Aktualisieren der Abfragen wir auch mit Arbeitsmappenschutz problemlos möglich.
Zugegeben, ist das keine optimale Lösung, aber vielleicht reicht das ja in deiner Situation und für deine Zwecke aus.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
Arbeitsmappe schützen funktioniert in Excel 2016 in der Form, dass ein Aktualisieren möglich ist, die Abfrage aber nicht geöffnet bzw. bearbeitet werden kann. Fehlermeldungen erscheinen nicht.
Aber welcher Kollege oder neugierige Anwender hat die Möglichkeit an meiner Abfrage herumwurschteln, wenn ich doch an einem Arbeitsplatz mit Kennwortschutz arbeite?
Danke für den Tipp
Hallo Bodo,
wenn Du – so wie in bei uns – auf einem Gruppenserver arbeitest, können alle Kollegen auf die dort abgelegten Daten zugreifen. Wenn dann die Rechte nicht eingeschränkt sind (z. B. nur Lesen) oder die Dateien nicht geschützt sind, kann das sehr wohl zu ungewollten Änderungen führen…
VG
Matthias
Hallo Bodo,
vielen Dank für diese Info. Irgendwie scheint das Verhalten in den verschiedenen Excel-Versionen sehr unterschiedlich zu sein. Ein anderer Leser, der mir geschrieben hat, setzt ebenfalls Office 2016 ein und bei ihm lässt sich bei aktiviertem Schutz die Abfrage nicht mehr aktualisieren. Alles sehr merkwürdig…
Zu Deiner Frage:
Das, was Matthias bereits geantwortet hat, wäre auch das Szenario, das ich für diesen Artikel im Kopf hatte. Und es kann natürlich notwendig sein, dass die Datei auch ganz bewusst an andere Kollegen verschickt wird, weil diese die Informationen benötigen. Dann würde der Arbeitsmappenschutz wieder sinnvoll sein.
Schöne Grüße,
Martin
Hallo Martin
Coole Lösung, die kannte ich noch gar nicht!
Top!
Ich kann bestätigen, dass sie in Excel M365 läuft.
Einziger Nachteil ist aber, dass der Benutzer den M-Code rauskopieren kann, aber zumindest kann er die vorhandene Lösung nicht mehr verändern.
BG, Hp
Hallo Hans Peter,
das freut mich ja ganz besonders, wenn ich einem alten Hasen wie dir auch mal etwas Neues zeigen kann 🙂
Aber jetzt hast du mich neugierig gemacht, denn an den M-Code kommt man damit doch eigentlich auch nicht ran…
Schöne Grüße,
Martin
Hallo Martin,
auf meiner heutigen Suche in google nach: „Power Query Abfrage schützen“, bin ich auf Deinen Beitrag hier gestoßen.
Ich kann nun auch bestätigen, dass eine Aktualisierung in XL2016 (Home and Office) in einer geschützten Arbeitsmappe problemlos möglich ist, aber (leider) auch ein Kopieren der Abfrage in eine ungeschützte Arbeitsmappe, womit man dann wieder freien Zugriff auf den M-Code hat.
Gruß Werner
.. , – …
Hallo Werner,
das stimmt natürlich, in der Kopie lässt sich auf den Code wieder zugreifen. Aber zumindest die originale Version ist einigermaßen geschützt vor unbedachten Änderungen.
Schöne Grüße,
Martin
Hallo, ich nutze Power Query noch nicht so lange. In meiner Abfrage gibt es bereits aus einem Export ein Attribut mit „Person und Freigabedatum“. Da der Workflow allerdings mehrere Stufen hat, kann jeder Bearbeiter unter der Person hinterlegt sein. Mich interessiert allerdings nur die Person der letzten Stufe. Wie könnte hier eine bedingte Formatierung aussehen? Könnte man der Formatierung Listen hinterlegen um dort die relevanten Personen
Hallo lieber Martin,
ja – das Problem mit aktiviertem Blattschutz ist mir auch aufgefallen. Ich nutze Office 2019.
Es bleibt einem also nichts anderes übrig, als mit einigen Zeilen Programmcode (VBA) Eingaben im Blatt zu verhindern.
Allerdings können dadurch leider die Spaltenbeschriftungen auch nicht vor Überschreiben geschützt werden.
Liebe Grüße
Hallo Tino,
das stimmt natürlich, die Spaltenbezeichnungen könnten immer noch überschrieben werden. Ganz wasserdicht geht es wohl nur mit VBA.
Schöne Grüße,
Martin
Hallo Martin.
Danke für die vielen Informationen zu diesem Thema, die ich bereits seit meinem ersten Lesen des Artikels immer wieder verwende 🙂
Sofern die Arbeitsmappe geschützt ist, funktioniert es tatsächlich einwandfrei.
Leider kann Power Query keine Daten in ein Arbeitsblatt spielen, bei dem der Blattschutz aktiv ist.
Aktuell habe ich eine Tabelle, bei der die Ergebnistabelle aus PQ direkt in das von den Kollegen verwendete Arbeitsblatt geladen wird.
Dieses Blatt sollte jedoch aus diversen Gründen einen Blattschutz erhalten, z.B. um zu verhindern, dass – ebenfalls im Blatt vorhandene – Formeln und Grafiken nicht mehr verändert/verschoben werden können.
Bisher habe ich das Problem gelöst, indem ich die Daten aus PQ in ein ausgeblendetes Arbeitsblatt geladen habe und in einem weiteren Arbeitsblatt eine Maske für die Kollegen erstellt habe, bei der mit Hilfe der Filterfunktion diese Tabelle ausgelesen wird.
Die Kollegen können in der Maske dann Häkchen setzen und/oder Suchbegriffe eingeben. Für jeden Kombination der obigen Eingaben gibt es dann eine Filterformel, die zusammengefasst in einer Ersterwertfunktion stehen.
Diese Lösung funktioniert diesmal aber nicht, da man bei der neuen Tabelle auf die reichhaltigen Standartfilterfunktionen der intelligenten Tabelle angewiesen ist, die ich auch nur ungern alle mühsam nachbauen möchte. Ich schätze mal, dass dann auch die Performance etwas leiden würde.
Gibt es Tips oder Tricks, wie man die Query-Ergebnisse in ein Arbeitsblatt inkl. Blattschutz laden kann?
Ich habe bereits versucht mit VBA den Blattschutz aufzuheben, die Abfrage zu aktualisieren und dann den Blattschutz wieder zu aktivieren, aber ich finde keine Funktion, die effektiv prüft, wann die Abfrage fertig geladen ist, so dass der Blattschutz immer zu früh wieder aktiviert wird.
Danke+Gruß
Sebastian
Hallo Sebastian,
es gibt leider keinen „normalen“ Weg, wie man PQ-Ergebnisse in ein geschütztes Blatt laden kann, denn der Blattschutz verhindert das Aktualisieren. Es bleibt also nur der von dir beschriebene Wege, per VBA den Schutz temporär aufzuheben.
Das Problem, dass man nicht weiß, wann die Abfrage fertig ist, lässt sich in der Regel dadurch lösen, dass man in den Abfrageeigenschaften die Option „Aktualisierung im Hintergrund zulassen“ deaktiviert. Damit sollte der Code erst dann weiterlaufen, wenn die Abfrage wirklich fertig ist.
Schöne Grüße,
Martin
Hallo Martin.
Vielen Dank für den Tip!
Das funktioniert nun tatsächlich und löst meine Probleme.
Jetzt muss ich den Kollegen nur noch erklären, dass es normal ist, wenn Excel für ein paar Minuten nicht mehr erreichbar ist 😉
Danke+Gruß
Sebastian