[gelöst] Excel Frage

@pufaxx
Wenn ich exakt die Schritte aus #18 mache kommt das hier heraus.
Die Datei Sheet1.xml.rels mit den Links habe ich im Editor geöffnet, die Links gefunden. Lassen sich ändern = ok.
Dann die Datei sheet1.xml.rels gespeichert. Dann zurück zum Hauptordner, Datei wieder verzippt und umbenannt.

Aber der Weg zurück klappt nicht. Habe jetzt 5-6 mal probiert.
Fehler siehe Screen.

Das klappt irgendwie vorne und hinten nicht.
Übrigens, der Weg über Funktion einfügen Hyperlink nutzte ich nicht. Viel einfacher.
Ich klicke in der Spalte "Link" auf Hyperlink einfügen, gebe das Ziel zur Datei an, fertig.

Ist erledigt. Habe den für meinen Zweck passenden VBA Code.
Je Datei dauert das ändern 1 min.

Hab's grad bei mir getestet - und funktioniert (Excel 2011).

Zum "Verpacken" wähle ich die Dateien aus und nehme dann "Senden an" -> "ZIP-komprimierter Ordner". Vielleicht funktioniert's ja nicht mit allen Kompressionsprogrammen? Die mitgelieferte Windows-Funktion und 7zip machen bei mir keine Schwierigkeiten.

Jedenfalls fein, dass Du's hinbekommen hast VBA ist in Deinem Fall wahrscheinlich eh besser als "Suchen" und "Ersetzen" im Texteditor. Dabei geht gerne etwas schief und man ersetzt zu viel.

Hab ich genauso gemacht. Nur den falschen Ordner gezippt anstatt der Dateien.
Nach einigem herum probieren klappt das jetzt.
Der Weg ist zwar mühsamer / umständlicher, aber es geht. Mit meinem VBACode geht das auch, nur schneller.
Aber...wieder etwas gelernt. Ich danke dir für die souveräne Lösung und deine Geduld.

Nur....warum Excel nicht bemerkt wenn da etwas verschoben wird ??
Ideal wäre wenn die Links dann direkt angepasst würden.

Wie du das mir der Funktion machst, hab ich noch nicht verstanden.
Ist zwar nicht mehr wichtig, aber wenn du magst erklär das man etwas genauer. Danke.

Naja ... woher soll Excel wissen, dass Du einen Ordner verschiebst? Besonders, wenn Excel oder die besagte Datei grad gar nicht geöffnet ist?



Wie auch immer: Ich brauch @work sehr selten Links, die mir direkt aus Excel heraus eine Datei öffnen. Aber WENN ich die brauche, dann müssen die funktionieren (z.B. weil sie auf Tarifverträge o.ä. verlinken, die bei uns auf dem Server liegen). Daher benutze ich lieber Formeln, die sind leichter zu handhaben.

Zur Demo ein paar Screenshots:

Hyperlink nur mit Pfad erzeugt einen Link mit dem kompletten Pfad als Linktext:

Man kann natürlich auch einen eigenen Text angeben, sieht dann so aus:

Oder man holt sich die Informationen/Bestandteile aus verschiedenen Zellen:


Hier ist zu sehen, dass ich für die Zelle B8 einen Namen, nämlich "MeinBilderordner" vergeben habe ...

... den ich hier in der Formel benutze:


Und alle Links, von deren Pfad "MeinBilderordner" ein Bestandteil ist, werden sich entsprechend anpassen, sobald Du statt "E:\Pictures\" irgendetwas Anderes eingibst.

... und hier die Excel-Datei dazu:

Anhang anzeigen ExcelHyperlinks.xlsx

Wahrscheinlich ist es ein Haufen Arbeit, Deine Datei auf die Funktion "HYPERLINK" umzustellen, aber man kann z.B. relativ leicht Dateipfade aus einem Ordner direkt in Excel einfügen. So dass Du DAS schon mal hättest ... Ich denke, der Thread könnte für Dich interessant sein -> https://www.drwindows.de/programme-...ipfad-explorer-excel-einfuegen-per-linke.html - da werden einige Varianten beschrieben.

Kannst ja mal ein Bisschen herum experimentieren. Eventuell kannst Du's Dir so oder so ähnlich ja in Zukunft etwas leichter machen?

ot:
Sehr wahrscheinlich wird einer der Admins meine Beiträge wieder zusammenführen wollen. Ich bitte aber darum, das dies Mal nicht zu tun - der Übersichtlichkeit wegen. Und ein Bisschen ist das ja auch wieder etwas Neues.

Whatever. Ich hab grad Urlaub, eigentlich wollte ich auch gleich in die Stadt, aber meine Waschmaschine ist noch nicht fertig. Und vorher hätte ich die Wäsche gerne aufgehängt ...

Deswegen hab ich gedacht "Jetzt bastele ich mal fix etwas, das aus einem Dateipfad einen verlinkten Dateinamen macht." - ohne VBA ist das jedoch überraschend kompliziert. Excel bietet zwar eine Funktion "FINDEN", bei der kann man blöderweise aber nicht einstellen, ob sie von links oder von rechts mit dem Suchen anfängt.

Um den Dateinamen zu ermitteln, braucht man aber nicht den ERSTEN "." in der Zeichenkette, sondern den letzten, weil's ja kein Problem ist, Dateinamen mit mehreren Punkten zu haben. Das gleiche gilt für den letzten Backslash.

Einfach die letzten vier Zeichen zu kappen ist auch keine Option, es gibt ja durchaus Dateiformate mit vier Buchstaben (".docx", ".docm", ".xlsx", ".xlsb", ".xlsm", ".indd" ...)

Hab zunächst selbst herum probiert, bin ein paar Mal gescheitert - aber nach Einsatz von Freund Google dann hier fündig geworden: https://supportnet.de/excel-formeln-buchstaben-von-rechts-suchen4989/

Persönlich vermeide ich Matrix-Formeln, wo's nur geht - denn in sogenannten "Datentabellen" (die man filtern und sortieren kann), funktionieren die einfach nicht. Deshalb musste ich bei der Seite auf die kompliziertere Variante zurückgreifen, siehe Absatz "Letzen Buchstaben ohne eine Matrixformel (Array-Formel) suchen:"

Den Platzhalter-String "#" habe ich durch ein Zeichen ausgetauscht, das in Dateinamen generell NICHT auftauchen kann. Während man Dateien problemlos "Bild#1.jpg", "Bild#2.jpg" nennen kann, geht "Bild/1.jpg", "Bild/2.jpg" halt eben garantiert NIE.

Echt doof, dass man nicht "von hinten" mit der Suche beginnen kann, so wird die ganze Geschichte (die eigentlich so einfach sein könnte) ohne Zwischenschritte reichlich unübersichtlich. Siehe Screenshot. Oben hab ich mit Zwischenergebnissen gearbeitet - in der ausgewählten Zelle wollte ich einfach mal schauen, wie's denn in EINER Formel aussehen würde.



Hm. Entsetzlich unelegant, würde ich behaupten. Argh! Also spätestens dann tut man sich mit benutzerdefinierten Funktionen (geht auch mit VBA) sehr viel leichter. Vor allem, weil man in VBA via Split Zeichenketten an bestimmten Fundstellen in einen Array aufteilen kann. Splittet man beim Backslash, hat man ein Array mit soundsoviel Elementen, das letzte Element wäre dann der komplette Dateiname inklusive Extension ...



Warum mache ich das eigentlich? Im Urlaub? Wissenschaftliches Interesse? Langsame Waschmaschine? Keine Ahnung.

Hier die Excel-Datei:

Anhang anzeigen ExcelHyperlinks - Linktext.xlsx

Ich mach's gewohnheitsmäßig meistens so, dass alle Zellen-mit-einer-Formel farbig hinterlegt sind, und alles, wo man "gefahrlos" etwas eingeben kann, ohne etwas kaputt zu machen, das ist bei mir immer weiß ... im Beitrag weiter oben habe ich das übrigens entgegen meiner gewohnten Praxis tatsächlich genau umgekehrt gemacht, aber da ging's mir darum, zu zeigen, was wie zusammengehört.

Nur....warum Excel nicht bemerkt wenn da etwas verschoben wird ??

Zum Vergrößern anklicken....

Das ist jetzt aber ne Fangfrage, oder? Als wenn Software was dafür kann, wenn Benutzer Daten nach unbekannt verschiebt und es nicht der Software mitteilt.

@pufaxx
Habe mir deine Beispiele mal näher angesehen. Was man mit Excel alles anstellen kann ist schon interessant.
Wenn man benutzerspezifische Lösungen braucht, muss man halt tiefer einsteigen.
Sehr beindruckend, was du da aufzeigst. Vielen lieben Dank dafür.
Ich habe eine Menge Excel Dateien, die sehr ineinander verschachtelt und verknüpft sind. Hyperlinks mit Datei oder Zell- Zielen
eigentlich eher weniger. Obwohl sich damit eine Menge machen lässt.
Mal ein Klassiker: kann jeder gern mal probieren. (das zielt jetzt auf Post 27)
Ordner TEST mit 2 Excel-Dateien, MAIER und SCHMITZ.
In der Datei MAIER steht in Zelle B1 ein Verweis auf Datei SCHMITZ Zelle B1.

Wird jetzt die Datei MAIER unter anderem Namen woanders gespeichert, bleibt der Bezug zur Datei SCHMITZ erhalten.
Wird allerdings die Datei SCHMITZ verschoben, geht der Bezug verloren, es sei denn
beide Dateien sind geöffnet. Dann bleibt der Bezug erhalten.

Ist aber in der Datei MAIER ein Hyperlink mit Ziel SCHMITZ und SCHMITZ wird unter anderem Namen woanders
gespeichert dann ändert sich die Linkadresse NICHT.
Beim nächsten Aufruf kann Excel die Datei nicht mehr finden.

Das bedeutet:
Zellbezüge werden bei Änderungen berücksichtigt, Hyperlinks nicht. Auch nicht wenn beide Dateien
geöffnet sind und dann woanders gespeichert werden. (Damit sich das ändert werde ich mich mal tiefer in deine Beispiele einarbeiten. Da funktioniert das wunderbar)

Lege ich z.B. eine Verknüpfung zu einer Datei in einen Ordner, verändere aber den Speicherort der Zieldatei, dann
wird die Verknüpfung automatisch angepasst.

So könnte man das im Excel doch auch machen. Ändert sich der Ort der Zieldatei = ändert die der Hyperlink ebenfalls automatisch.
Habe übrigens endlos viele solcher Bezüge in Zellen. Muß folglich immer aufpassen, auf die Verweise zu anderen
Tabellen oder Zellen.

ot.
@Bernd
Hat dein Post 27 jetzt irgendetwas mit dem Thema zu tun, geschweige zur Lösung? Nein.
Unverständlich das Leute wie du in einem so hervorragenden Forum geduldet werden. Ist es so schwer sachlich zu bleiben?

... es ist grundsätzlich ein Unterschied, ob Du Dich in einem Excel-Dokument auf Ergebnisse (oder Inhalte irgendwelcher Zellen) aus einem anderen Excel-Dokument beziehst (denn da versucht Excel, irgendwie "hinterher zu kommen") - oder ob Du einfach irgendwelche Links irgendwo reintust.

Bei Letzterem weiß Excel von vornherein, dass es nicht hinterherkommt.
Bei Ersterem kann's aber genauso schief laufen, je nachdem, was Du wie und wann und in welcher Reihenfolge geöffnet hast.

Parallele zu Serienbriefen. Du kannst einen Serienbrief öffnen, der sich auf Deine Excel-Datei (lalala-Beispiel) "Kunden-Bis-Ende-Dezember-2019.xlsx" bezieht. Öffnest Du erst danach Deine "Kunden-Bis-Ende-Dezember-2019.xlsx", wirst Du eine lustige Fehlermeldung erhalten mit dem Angebot, diese "Kunden-Bis-Ende-Dezember-2019.xlsx" schreibgeschützt zu öffnen.

Per se gar nicht schlecht.

Weißt Du aber schon, dass der Serienbrief einem etwas geänderten Datensatz folgen soll, ist es besser, erst die Excel-Datei zu öffnen und dann erst den Serienbrief.

So ganz doof ist das alles nicht. Aber - und das ist nicht die Schuld der Programmierer - man muss halt schon vorausdenken. Das "zu Ändernde" muss man immer zuerst öffnen. Das "resultierende" (also beispielsweise den Serienbrief) - den öffnet man erst, nachdem man die "Eingabe-Datei" geöffnet hat.

Ansonsten - Deine Ideen sind ja super. Bloß: Wie soll man das umsetzen? Ist doch keine Option, dass Excel im Hintergrund immer mitstartet und all Deine Datei-Verschiebe-Aktionen mitschreibt. Dabei gleichzeitig alle Dateien durchsucht, die davon betroffen sein könnten, diese öffnet, bearbeitet, neu abspeichert ...? Nee ... ich glaub, das geht einfach nicht. Ich wüsste erstens nicht wie - und ob das so praktisch ist, weiß ich auch nicht.

Sobald einem bei so etwas zu viel aus der Hand genommen und automatisiert wird, funktionieren vielleicht x Sachen viel-viel-viel leichter, andere Sachen gehen dadurch aber sehr-sehr-sehr wahrscheinlich komplett kaputt. Da ist mir ein Bisschen mehr "Handarbeit" lieber - einfach, weil die Ergebnisse besser voraussagbar sind.

Nachtrag: Excel ist schon überraschend gut, wenn's darum geht, ganze Bereiche woandershin zu verschieben oder gar in ein neues Arbeitsblatt zu kopieren. Die Formeln spucken dann nicht irgendeinen Müll oder #WERT aus, die funktionieren sehr zuverlässig weiterhin. Das klappt aber eben auch bloß, weil Excel währenddessen geöffnet ist und eine Chance hat, "mitzudenken". Alles, was Du außerhalb davon anstellst - ja - geht eben meistens nicht.

Ob Du nun z.B. kurz vor Monatsende Deine Kontonummer änderst und Dein Arbeitgeber nichts davon weiß ... oder ob Du irgendwelche Dateien verschiebst ... oder umbenennst ... es ist doch immer so: Wenn die eine Hand nicht weiß, was die andere grad tut oder getan hat - geht immer irgendetwas schief.

Ja du sprichst mir aus der Seele. Ich mache viel mit Excel, es ist schon echt gut.
Aber manchmal, man denkt nicht grad dran, verschiebt etwas und schon sind die Probleme da.
Wie in meinem Post 1. Hatte die Datei mit den "Links" völlig vergessen. Aber als ich sie nach Monaten wieder brauchte, oh oh.
-
Bin leider bei weitem nicht so fit in Excel wie du, aber du hast mir neue Wege eröffnet, die werde ich in den nächsten Wochen versuchen umzusetzen. Wenn's hakt, weiß ich ja wo ich Hilfe erwarten kann.

Danke und schönen Abend.

Da wollte ich eigentlich grad schimpfen ...

VT12 schrieb:

Lege ich z.B. eine Verknüpfung zu einer Datei in einen Ordner, verändere aber den Speicherort der Zieldatei, dann wird die Verknüpfung automatisch angepasst.

Zum Vergrößern anklicken....

... weil ich dachte, so funktioniert das nicht. Denn @work (noch Windows 7) funktioniert das auch nicht. Da würde eine Fehlermeldung kommen. Oft genug gehabt. Mag vielleicht auch damit zu tun haben, dass ich beinahe nur mit Dateien arbeite, die auf dem Server liegen.

Unter Windows 10? Da hab ich wieder was dazu gelernt. Da geht das tatsächlich. Ich hab eben eine "hans.txt" mit Inhalt "HANS" erzeugt, eine passende Verlinkung dazu - und die "hans.txt" in "klaus.txt" umbenannt. What! Doppelklick hat mich dann zur "klaus.txt" mit Inhalt "HANS" geführt.

@Allgemeinheit: Das finde ich jetzt spannend. Ging das mit Windows 7 (sofern man keine Server-Dateien verlinkt) auch? Oder ist das neu bei Windows 10? Bisher war ich immer der Meinung, dass ein Link nicht mehr funktioniert, sobald man die verlinkte Datei umbenennt oder verschiebt. Könnte das was mit indizierten Laufwerken zu tun haben? @work weiß ich gar nicht, was da wie eingestellt ist, hab keine Rechte für nix. Alle Versuche, egal-was herauszufinden enden mit der Aufforderung, das Admin-Passwort einzugeben. Wobei man witzigerweise vergessen hat, das BIOS mit nem Passwort zu schützen.

[Bei solchen Themen wundere ich mich immer wieder, wie oft "kein Home-Office mehr" für mich für Verwunderung sorgt. Einfach, weil ich's daheim noch nie ausprobieren musste]

Ich hab das just letzte Woche (vorm Urlaub) gehabt. Alle möglichen Dateien für einen potentiellen neuen Mitarbeiter, der während meines Urlaubs sein Vorstellungsgespräch haben sollte ausgefüllt. Falls es zu einem Vertrag kommt. "Nachname, Hermann - Dienstvertag.docx", "Nachname, Hermann - Gleitzone.docx", "Nachname, Hermann - Hausordnung.docx." und so weiter angelegt. Verlinkungen zu all diesen Dateien in den Server-Ordner meiner Kollegin geschoben. Verlinkungen haben funktioniert.

Stellte sich raus - Geburtsort Chicago, der heißt nicht "Hermann", sondern "Herman Alex". Dateien geändert, entsprechend umbenannt - alle Verlinkungen führten ins Leere. Musste ich also neu verlinken. Kollegin: Windows 10, ich: Windows 7. Liegt das an Windows 7 allgemein oder daran, dass die Dateien auf dem Server lagen?

@pufaxx
Zum ersten Teil deines Post 31, ich war auch erstaunt das die Verknüpfungen sich automatisch anpassen.
Indiziert habe ich nichts, weil ich die Windows Suche eh deaktiviert habe.
Solange das Laufwerk gleich bleibt wird die Datei trotzdem korrekt gefunden.
Schiebe ich aber auf ein anderes Laufwerk, kommt die Fehlermeldung.

Habe mal begonnen mit der Umstellung gemäß deiner Methode. Siehe da, das funkt super.
Sobald ich den Speicherort, der in Zelle A2 steht ändere, die Dateien dort auch abgelegt sind, funktionierten
die LinKs in der Spalte A. Phantastisch.

Aus der Datei xlsx muss aber eine xlsm werden, wegen dem VBA Code. Ausserdem habe ich deine
Beipieldatei, die eine gute Basis war, etwas umgebaut.
Weil ich in meiner Tabelle unbedingt die Spaltenköpfe zur Sortierung oder Filterzwecken brauche, hab
ich den Pfadbezug mit dem Namensmanager angepasst und in ein eigenes Sheet verlegt.
Da ich die Sortierungen ebenfalls per VBA Code erledigen lasse, ging da nicht anders.
Die jeweilige aktuelle Sortierung wird in der Kopfspalte immer wenn sie aktiv ist FARBIG angezeigt.
Ändere ich die Sortierung, wird immer die passende Kopfspalte farbig.
Ändere ich den Bezugs Pfad zu den Tracks, wird mit einer MSG Box gewarnt.

Jetzt ist das optimal.
Danke für deine Tips.

Benutzt Du "Datentabellen"?

Die haben den Vorteil, dass die "von sich aus" bereits filter- und sortierbar sind, außerdem erleichtern die einem das Schreiben von Formeln. Statt irgendwelche Zellbezüge mit Spaltenbuchstaben einzutragen, kann man die Spaltennamen benutzen.

So wird aus einem =HYPERLINK(F4;B4) beispielsweise ein =HYPERLINK([@PfadKpl];[@DName])

Das wird insbesondere dann interessant, wenn man einige "Eingabe-Tabellen" hat, die dann in anderen "Auswertungs-Tabellen" Ergebnisse liefern müssen. Wenn Du meinetwegen sicher sein kannst, dass in all Deinen Eingabe-Tabellen die Spalte mit der Personalnummer"PersNr" heißt, hast Du keine Probleme, in Deiner Auswertungstabelle alle Treffer zu suchen, für die "TabellenNameLalala[PersNr]=[@PersNr]" ist. Und zwar ohne hin und her zu switchen, weil Du nicht mehr genau weißt, ob überall die Personalnummer in Spalte C ist. Die muss bloß PersNr heißen. Ob die Spalte hier und da mal verschoben wurde, ist dann egal.

So werden dann Konstruktionen möglich à la =INDEX(Mitarbeiter[Einsatzort];VERGLEICH("12345";Mitarbeiter[PersNr];0)) <-[durchsucht die Spalte "Einsatzort" in der Tabelle "Mitarbeiter" und gibt den ersten Treffer bei PersNr 12345 aus]

Oder =INDEX(Mitarbeiter[Einsatzort];VERGLEICH([@PersNr];Mitarbeiter[PersNr];0)) <-[durchsucht die Spalte "Einsatzort" in der Tabelle "Mitarbeiter" und gibt den ersten Treffer von der PersNr aus, die in der aufrufenden Tabelle in der selben Zeile unter PersNr eingetragen ist]


Insgesamt sind Datentabellen einfach großartig, die machen einem das Leben (und die Formeln) deutlich leichter. Weil man Spalten eben so benennen kann, dass die Formeln - taugliche Benennung vorausgesetzt - sehr leicht nachvollziehbar sind. Und man neue Formeln ohne viel Gesuche recht einfach reinhacken kann. Überdies braucht man Formeln nicht "runterzuziehen", fügt man eine neue Spalte ein, enthält jede Spalte in der neuen Zeile dieselbe Formel wie die entsprechende Spalte in der Zeile drüber. Bedingte Formatierungen funktionieren so auch deutlich besser.

Tipp: Hat ein Spaltenname ein Leerzeichen, wird's mit den Klammern etwas anders. Dann muss aus [@PersNr] ein [@[Pers Nr]] werden, Mitarbeiter[Pers Nr] kommt aber weiterhin mit zwei Klammern aus. Warum auch immer.

Nachteil an Datentabellen: Man kann darin keine Zellen verbinden. Man kann keine Matrix-Funktionen verwenden.

Im Anhang die Datei von neulich mit "Datentabellen":

Anhang anzeigen ExcelHyperlinks - Datentabellen.xlsx

Ich finde, Datentabellen lohnen sich. Übrigens kann man - wenn die Formel denn funktioniert - problemlos Spalten verschieben, umbenennen, Tralala (auch ein großer Vorteil) - Formeln aus anderen Tabellen (nicht unbedingt aus anderen Dateien) funktionieren weiterhin und geben keinen groben Mist aus. Echte Empfehlung.

ot:
Thema Verlinkungen allgemein - Heute @work habe ich echt vergessen, mal auszuprobieren, ob Verlinkungen auf meiner lokalen Festplatte noch funktionieren, wenn ich die Zieldatei umbenenne. Wie's oft so ist, nach dem Urlaub - da prasselt immer alles Mögliche auf einen ein.

Mache ich morgen. Jedenfalls bin ich ziemlich dankbar für unseren Dialog, weil ich - ganz ehrlich - NIE auf die Idee gekommen wäre, dass Windows so etwas inzwischen erkennt und sich drauf einstellt. Privat musste ich das auch noch nie ausprobieren. Klar benutze ich dann und wann mal Verlinkungen (im Startmenü oder im QuickLaunch-Ordner) - aber die verlinken auf .exe-Dateien, bei denen sich der Name auch nach Updates nicht ändert. Und ansonsten weiß ich, wo ich was bei mir finde.

Bloß @work muss ich eben dann und wann "angefangene" Sachen von mir von anderen Kollegen zu Ende bringen lassen. Und bevor ich die Dateien in irgendeinen Ordner "URLAUBSVERTRETUNG PUFAXX" schiebe und nachher wieder alles in mein System zurück sortieren muss, kommen in den Vertretungsordner eben nur Verlinkungen rein. Dann müssen meine Kollegen nicht groß suchen, und ich hab später auch deutlich weniger Gefummel. Nur umbenennen darf ich die Zieldateien eben nicht, sonst laufen deren Links ins Leere.

Krass. Seit 2011 arbeite ich in dem Laden. Ist mir echt noch nie aufgefallen, dass Zieldateien-Umbenennen inzwischen anscheinend funktioniert.

Jut, also viel Spaß beim Basteln - und beste Grüße.