Eine Liebeserklärung an MS Excel
Schöne Frage zu Access. Dort kann man Tabellen – auch mit deren Verknüpfungen – leicht in eine XML-Datei exportieren – die Einstellungen sind komplexer als beim Export Excel nach XML:
Allerdings – so lautet seine Frage: wie kann man den Namen des Wurzelelements dataroot ändern?
Er hat recht – ich finde keine Einstellung hierzu. Also doch per Hand oder mit ein paar Zeilen VBA-Code …
Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:
Public Sub ListPivotTables()
Dim c As Long
Dim b As Worksheet
Dim o As PivotTable
Dim St As Worksheet
On Error Resume Next
Set b = ThisWorkbook.Worksheets.Add
c = c + 1
b.Cells(c, 1).Value = "Name"
b.Cells(c, 2).Value = "Quelle"
b.Cells(c, 3).Value = "Aktualisierung"
b.Cells(c, 4).Value = "Arbeitsblatt"
b.Cells(c, 5).Value = "Bereich"
b.Cells(c, 6).Value = "MDX"
For Each St In ThisWorkbook.Worksheets
For Each o In St.PivotTables
c = c + 1
b.Cells(c, 1).Value = o.Name
b.Cells(c, 2).Value = o.SourceData
b.Cells(c, 3).Value = o.RefreshDate
b.Cells(c, 4).Value = o.Name
b.Cells(c, 5).Value = o.TableRange1.Address
b.Cells(c, 6).Value = o.MDX
Next
Next
End Sub
Ich habe es ein klein wenig überarbeitet. Das Ergebnis:
Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:
Hallo Herr Martin
Es kommt wieder mal eine Frage aus der Schweiz.
Für den damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.
Haben Sie dazu eine Idee?
Hallo Herr S.,
im Ordner _rels befindet sich eine XML-Datei mit Namen .rels. In ihr muss eingetragen werden, dass im Ordner ribbon\customUI die Datei customUI1.xml mit der Ribbon-Definition liegt, beispielsweise so:
<Relationship Id=“rId2″ Type=“http://schemas.microsoft.com/office/2007/relationships/ui/extensibility“ Target=“ribbon/customUI/customUI1.xml“/>
Die Id muss eindeutig sein!
Liebe Grüße
Rene Martin
Irgendwie doof. Ich habe eine Tabelle, in der sich mehrere Kommentare (Notizen) befinden.
Nun lautet die Anweisung, dass ich diese Kommentare in eine eigene Spalte schreiben soll. Stellt man im Dialog „Seite einrichten“ ein, dass Kommentare am Ende der Tabelle erscheinen, kann man das im Drucken-Dialog sehen:
Au, prima, denke ich – und speichere die Datei als PDF. Was passiert?
Die Kommentare sind NICHT im PDF!
Anders jedoch, wenn ich einen PDF-Drucker installiert habe – dann kann ich die Datei drucken und Kommentare erscheinen auf dem letzten Blatt.
Warum mache ich das? Nun – das PDF kann in Word geöffnet werden; die Kommentare entnommen und weiter verarbeitet werden.
Und was mache ich, wenn ich keinen PDF-Drucker habe?
Die Antwort: ich kann die Datei mit der Endung ZIP umbenennen, entzippen und die Datei comments1.xml aus dem Ordner xl öffnen. Dort finden sich auch die Kommentartexte, die ich leicht entnehmen kann:
Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:
Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:
Der Code:
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group idMso="GroupEditingExcel" visible="false" >
</group>
</tab>Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:
Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnKey "^f", "BitteNicht" ' suchen
Application.OnKey "^h", "BitteNicht" ' ersetzen
End SubBeim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:
Private Sub Workbook_Activate()Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f", "" ' suchen
Application.OnKey "^h", "" ' ersetzen
End SubEbenso beim Ereignis Workbook_Deactivate.
Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f" ' suchen
Application.OnKey "^h" ' ersetzen
End SubDANN ist das Ereignis wiederhergestellt!
Montag ist Datentypentag.
Man kann Firmennamen, Städte- und Ländernamen in Datentypen umwandeln. Mit einem Klick auf das kleine Symbol, über das Kontextmenü oder der Tastenkombination [Strg] + [Umschalt] + [F5] kann man sich die „Karte“ dazu anzeigen lassen: ein Foto, ein Link zur Quelle und weitere Informationen:
Diese Informationen werden im Archiv der Excelmappe gespeichert und können dort im Ordner xl/richData in der Datei rdrichvalue.xml eingesehen werden:
Warum eigentlich nur sehen?
Richtig: DORT kann man die Informationen auch verbiegen, beispielsweise den Link:
Und so verweist der Link nun auf eine ganz andere Seite.
Okay: Nach Aktualisierung wird diese Information wieder „zurück gesetzt“.
Danke an M.L. auch Aachen für diesen Hinweis.
Hallo Herr Dr. Martin,
ich habe mich nach längerer Pause mal wieder ans Ribbon Design gewagt.
Leider sagt mir Excel jedes Mal nach dem Zippen und wieder in .xlsm umbenennen, die Datei sei beschädigt. Das passiert auch wenn ich keine Änderungen am entzippten Ordner vornehme (sowohl am Arbeitsrechner als auch am privaten Rechner)
Haben Sie vielleicht nochmal einen Tipp? Ich scheine irgendwas falsch zu machen…
Liebe Grüße und vielen Dank
NF.
Hallo Herr Fritz,
mit Sicherheit haben Sie einen Fehler in der XML-Ribbon-Datei. Verwenden Sie einen guten XML-Editor? Er muss validierend sein.
Ich verwenden Visual Studio – gibt es auch als kostenlose Version. Dort ist die Schema-Datei für das MSO integriert.
Schöne Grüße Rene Martin
Hallo,
ich habe ein Tool gebaut wo ich im Namensmanager auf die Funktion Bereich.Verschieben nutze, um das Diagramm dynamisch zu gestallten.
Nun möchte Ich die Mappe vor neugierigen Blicken schützen. Über Blatt- und Arbeitsmappeschützen klappt das auch ausreichend, allerdings ist der Namensmanager komplett einsehbar.
Haben Sie eine Idee, wir ich den Namensmanager verbergen kann?
Ich freue mich von Ihnen zu hören.
Viele Grüße,
Hallo Herr W.,
das Ausblenden funktioniert. Leider nicht das Symbol, aber die Gruppe „Definierte Namen“. Haben Sie die Excel-Datei schon einmal entzippt und nachgeschaut?
In der Datei „.rels“ im Ordner _rels muss ein Verweis auf die Datei stehen:
<Relationship Id=“rID4″ Type=“http://schemas.microsoft.com/office/2006/relationships/ui/extensibility“ Target=“ribbon/ribbon.xml“/>
In der Datei ribbon.xml wird die Gruppe ausgeblendet:
<ribbon startFromScratch=“false“>
<tabs>
<tab idMso=“TabFormulas“>
<group idMso=“GroupNamedCells“ visible=“false“>
</group>
</tab>
</tabs>
</ribbon>
Kommen Sie damit klar?
Schöne Grüße
Rene Martin
PS: Der Anwender kann den Namensmanager immer noch über [Strg] + [F3] öffnen. Das könnte man mit VBA wegprogrammieren. Dann könnte er immer noch ein Symbol in die Symbolleiste für den Schnellzugriff legen …
Habt ihr schon einmal Ribbon selbst mit einer XML-Datei erstellt. Ein validierender XML-Editor ist wichtig (wer Visual Studio hat, ist gut beraten).
Man fragt sich, welcher Praktikant den Befehl checkBox und nicht Checkbox (auch nicht CheckBox) genannt hat. Buttons habe das Attribut size mit den Werten „large“ und „normal“. Hum!
Schade. Ich liebe Power Query! Gut, durchdacht, clever, scheinbar fehlerfrei. Jedoch: eine Sache habe ich gefunden, die mich sehr irritiert.
Importiert man eine XML-Datei, in der sich Umlaute befinden, werden diese nicht korrekt angezeigt und lassen sich auch noch transformieren. Ich habe weder einen Schalter (Gebietsschema) noch einen anderen, cleveren Ersetzen-Befehl gefunden. Schade!
Soeben fragt mich Excel in welchem Format ich denn gerne den Standard verwenden möchte. Hä?!? – habe ich doch schon angegeben!
Ein Klick auf dem Link
führt zum Kleingedruckten!
UFF!
Ich verstehe es nicht. In einer (großen) Datei befinden sich mehrere Verknüpfungen. Ich lösche sie. Alle – bis auf eine. Sie ist störrisch und lässt sich nicht löschen:
Die Suche in der Datei liefert keinen Erfolg:
Es gibt auch keinen Namen, keine bedingte Formatierung, keine Datenüberprüfung, in der die Verknüpfung zu finden wäre.
Ich öffne das XML-Archiv und suche dort. DORT werde ich fündig:
Den Knoten löschen, das Archiv zippen – schon ist die Verknüpfung weg. Ich wüsste ja gerne wie so etwas passieren kann. Und – gibt es nicht einen bequemeren Weg die Verknüpfungen zu entfernen?
Sehr geehrter Herr Dr. Martin,
ich bin großer Fan Ihrer Tutorials (und Ihrer Westen) früher auf video2brain, heute auf LinkedIn.
Heute wende ich mich mit einer Frage an Sie, auf die ich in den Weiten des Internets keine Lösung gefunden habe.
Ich habe ein EXCEL Datei, mit der wöchentlich die „Performance“ an verschiedenen Parametern gemessen wird.
Hier wird (auf ca. 50 Tabellenblättern in jeweils ca. 40 Zellen) mit „Bedingter“ Formatierung“ gearbeitet, um optisch die jeweiligen Werte zu bewerten.
Gibt es eine Möglichkeit, die bestehende „Bedingte Formatierungsregel“ zeitsparend für die ca. 2.000 Zellen zu ändern?
Vielen Herzlichen Dank im Voraus für Ihre Hilfe.
Hallo Herr S.,
ich fürchte, DA bringen Sie Excel an die Grenzen. Man kann so etwas programmieren (wenn Sie sehr viele einzelne bedingte Formatierungen haben). Sie könnten das Dokument „entzippen“ und dort in der XML-Datei des Tabellenblattes die bed. Formatierung ändern (kennen Sie das? – die conditional formating befindet sich ganz am Ende – muss allerdings auch für jeder Formatierung einzeln vorgenommen werden):
Sonst weiß ich leider keine Möglichkeit ALLE bedingten Formatierungen schnell zu ändern.
Blöder Tipp: manchmal geht es schneller, wenn sie alle bed. Formatierungen löschen und dann neu erstellen.
Heute in der Excel-Schulung wollte ich den Inquire vorstellen. Ein praktisches, nützliches und gutes Werkzeug. Und dann das:
Excel: nein! Diese Arbeitsmappe ist NICHT beschädigt! Und … die IT ist so weit …
Excel hat ein grauenvolles Speichermanagement von Dateien in Bezug auf Formatierungen.
Ein manuelles „Wegformatieren“ nützt nichts – dieser Bereich bleibt im benutzten Bereich ([Strg] + [Ende]). Man muss die Zeilen löschen oder „Alles löschen“.
Beispiel: eine Datei hat eine Dateigröße von 9 Mbyte. Sie ist von Zelle A1 bis U1048576 gefüllt!?!
Löscht man von Zelle A3001 bis zum Ende der Datei nicht nur die Zeilen, sondern auch die Formatierungen, so quittiert Excel dies mit einer Dateigrößenänderung auf 48 Mbyte!?!
Der Grund:
Wenn in einer leeren Excelmappe Zeile 2 markiert und gelb formatiert wird, hat die gespeicherte Datei eine Größe von 7,62 KByte. Markiert man dagegen von B2 bis XFD2 verlangt Excel dafür eine Dateigröße von 45,7 KByte.
Der Grund findet sich in XML, genauer in der Datei: xl\worksheets\sheet1.xml. Der XML-Code der Datei mit der durchgehenden Formatierung sieht folgendermaßen aus:
[…]
<sheetView tabSelected=“1″ workbookViewId=“0″>
<selection activeCell=“A2″ sqref=“A2:XFD2″/>
</sheetViews>
<sheetFormatPr baseColWidth=“10″ defaultRowHeight=“12.75″ x14ac:dyDescent=“0.2″/>
<sheetData>
<row r=“2″ s=“1″ customFormat=“1″ x14ac:dyDescent=“0.2″/>
</sheetData>
[…]
Der XML-Code der Datei, in der Zelle B2:XFD2 formatiert wurde:
<sheetView tabSelected=“1″ topLeftCell=“XER1″ workbookViewId=“0″>
<selection activeCell=“XFC2″ sqref=“A2:XFC2″/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth=“10″ defaultRowHeight=“12.75″ x14ac:dyDescent=“0.2″/>
<sheetData>
<row r=“2″ spans=“1:16383″ x14ac:dyDescent=“0.2″>
<c r=“B2″ s=“1″/>
<c r=“C2″ s=“1″/>
<c r=“D2″ s=“1″/>
<c r=“E2″ s=“1″/>
<c r=“F2″ s=“1″/>
<c r=“G2″ s=“1″/>
<c r=“H2″ s=“1″/>
<c r=“I2″ s=“1″/>
<c r=“J2″ s=“1″/>
<c r=“K2″ s=“1″/>
[…]
<c r=“XEW2″ s=“1″/>
<c r=“XEX2″ s=“1″/>
<c r=“XEY2″ s=“1″/>
<c r=“XEZ2″ s=“1″/>
<c r=“XFA2″ s=“1″/>
<c r=“XFB2″ s=“1″/>
<c r=“XFC2″ s=“1″/>
<c r=“XFD2″ s=“1″/>
</row>
</sheetData>
Insbesondere blähen bedingte Formatierungen Excel-Arbeitsmappen auf. Nicht gut: jede Zelle einzeln mit einer bedingten Formatierung versehen, da dieser Formatierung folgender XML-Code
<conditionalFormatting sqref=“H2″>
<cfRule type=“iconSet“ priority=“24766″>
<iconSet iconSet=“5Rating“>
<cfvo type=“percent“ val=“0″/>
<cfvo type=“percent“ val=“20″/>
<cfvo type=“percent“ val=“40″/>
<cfvo type=“percent“ val=“60″/>
<cfvo type=“percent“ val=“80″/>
</iconSet>
</cfRule>
<cfRule type=“iconSet“ priority=“24765″>
<iconSet iconSet=“5Rating“>
<cfvo type=“percent“ val=“0″/>
<cfvo type=“num“ val=“120″/>
<cfvo type=“num“ val=“140″/>
<cfvo type=“num“ val=“160″/>
<cfvo type=“num“ val=“180″/>
</iconSet>
</cfRule>
</conditionalFormatting>
zugewiesen wird.
Lösungen beim Aufblähen durch Formatierungen:
* Zeilen löschen
* Spalten löschen
* Formatierungen löschen:
* Mit Inquire „Übermäßige Zellformatierungen entfernen“
* Die Datei als Excel-Binärarbeitsmappe (XLSB) speichern. Dies verkleinert die Dateigröße um zirka 50%.
Formate vermeiden (so nicht!):
Schon erstaunt.
Ich schreibe in eine Excelarbeitsmappe einen Text.
Ich speichere die Datei. Benenne die Endung um in „.zip“. Ich entzippe die Datei, suche im Ordner „xl“ nach der Datei „sharedStrings.xml“ und öffne sie in einem guten XML-Editor:
Ich will es wissen! Ich füge weitere Tags hinzu:
Speichere die XML-Datei, zippe das Archiv und sehe nach. Kein Fehler beim Öffnen der Datei (!), kein weiterer Text wird angezeigt. Das Ergebnis sieht so aus:
Ich schaue nach: Datei / Informationen / Auf Probleme überprüfen – Excel findet keine Probleme:
Keine Benutzerdefinierten XML-Daten. Ich schaue im Inquire nach – auch dort wird nichts gefunden:
Das heißt: Hacker aller Länder: versteckt Eure Daten in XML-Elementen! Das findet kein Mensch!
Randbemerkung: okay, okay – man darf nicht jeden beliebigen Knoten an jeder Stelle platzieren. Aber das hat man schnell herausgefunden, was man darf.
Ich bin sehr irritiert.
Auf unseren letzten Excel-Stammtisch haben wir folgendes Phänomen festgestellt:
Tragen Sie in eine Zelle den Wert 8625,21 ein. Speichern Sie die Datei. Ändern Sie den Dateinamen, indem Sie „.zip“ als Ende einfügen. Entzippen Sie die Datei. Öffnen Sie die Datei sheet1.xml, die Sie im Ordner xl/worksheets finden. Und was sehen Sie dort?
8625.2099999999991
???
Das heißt: nicht nur beim Rechnen und Herunterziehen hat Excel interne Rundungsfehler, sondern bereits bei der Eingabe. Und das schon bei „kleinen“ Zahlen.
Finden wir das komisch?
Guten Tag Herr Martin,
vielen Dank für Ihre informative Seite, welche mir nun bereits schon einige Male helfen konnte. Dieses Mal wurde ich leider trotz intensiver Suche im gesamten Internet nicht fündigt. Es geht um folgendes Problem:
Eine Kollegin hat eine Excelmappe in der mehrere Zellen über Bedingte Formatierungen eingefärbt werden. Die werden jedoch beim drucken nicht übernommen. Schon der Druckvorschau (und auch beim drucken selber die Farben nciht verwendet, sondern alle Zellen mit weißen Hintergrund dargestellt bzw. gedruckt.
die bedingte Formatierung
aber leider nicht sichtbar …
Hallo Herr O.,
Mir ist folgendes aufgefallen:
Wenn ich von Ihrer Datei ein Tabellenblatt in eine neue Datei verschiebe (egal welches Blatt), habe ich dort den gleichen Effekt. Oder umgekehrt: Bedingte Formatierung „reagiert nicht“.
Ich habe die Datei im xlsx-Format gespeichert, entzippt und „reingeschaut“ und verglichen mit einer „normalen“ Excel-Datei.
Ich weiß nicht genau an welcher Stelle, aber bei der styles.xml (im Ordner xl) finde ich Unterschiede zur „normalen“ Datei.
Ich habe nun die gesamte Datei durch „meine“ styles.xml ersetzt, geöffnet – damit sind leider sämtliche Formatierungen weg, aber nun funktioniert die bedingte Formatierung wieder.
Das heißt: durch irgendein Speichern/Austausch/Öffnen, … wurde etwas in den Formaten „zerschossen“. Und zwar in den Formaten der Datei (nicht des Tabellenblattes). Ich weiß nicht wann, bei welcher Aktion und nicht genau was. Aber wenn Sie die Datei neu formatieren, können Sie mit Ihr arbeiten.
Wenn Sie einen Blick in meine Datei werfen, erhalten Sie eine Meldung, dass etwas nicht stimmt. Klar: ich habe ja die styles.xml ausgetauscht.
schöne Grüße
Rene Martin
PS: Wenn ich ganz viel Zeit habe, schaue ich mir Ihre Styles-Datei noch einmal genauer an – vielleicht kann ich die Stelle lokalisieren, wo der Fehler auftritt.
Verblüfft war ich schon. Ein Teilnehmer der letzten Excel-Schulung zeigte mit eine Datei, die er aus dem USA erhalten hat. Darin befanden sich mehrere Zellen mit bedingten Formatierungen:
Ein Klick auf die „rote“ Bedingung (Zellwert<=0,81) zeigte allerdings die „grüne“ Bedingung (Zellwert = 1) an. Ich war verblüfft!
Die Lösung fand ich als ich das Dokument entzippte und mit die Datei sheet1.xml im Ordner xl\worksheets anschaute. Dort war bei mehreren Bedingungen (nicht bei allen!) das Attribut priority auf den gleichen Wert gesetzt: mehrmals auf 4, auf 5, auf 6, … Natürlich müssen dort unterschiedliche Werte stehen. Per Hand geändert, die Dateien gezippt – und schon lief die bedingte Formatierung wieder.
Die XML-Datei
Ich weiß zwar nicht, wann das Problem entstanden ist (USA – Deutschland) oder Excel 2007 – 2010, … aber immerhin – Problemursache gefunden und Problem gelöst.