Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.05.2024 20:08:47
Datum/Zeit in einer Zelle fixieren über Makros
17.05.2024 18:14:48
Aaaah! Tabelle erstellen mit Index
17.05.2024 17:37:33
letztes Zeichen in Zelle entfernen
17.05.2024 17:07:32
Problem bei Diagrammerstellung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1952to1956
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1952to1956
1952to1956
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Teil von Verweisen durch Tabellenblattname ersetzen

Teil von Verweisen durch Tabellenblattname ersetzen
13.11.2023 10:34:41
Jacky151188
Hallo Community,

zum Jahresende s erhalten wir von 400 Unternehmen einen beantworteten Fragenkatalog. Um alles in einer Datei zu sammeln, besteht jedes Blatt aus den Daten einer Gesellschaft. Ich lege einmal ein Blatt an und ersetze dann die Formeln durch die nächste Gesellschaft, im nächsten Tabellenblatt. Das bedeutet dann 400 mal suchen und ersetzen.. das geht doch bestimmt schneller mit VBA?

Genauer:
Ich suche nach einem VBA Code, der in den Tabellenblättern automatisch den Teil der Formeln ersetzt, der die Verweise auf andere Dateien enthält und zwar durch den Tabellenblattnamen (der den Namen des Unternehmens enthält).

Beispieldatei: https://www.herber.de/bbs/user/164233.xlsx

Vielleicht kann mir jemand helfen, ich bin kein VBA Profi...leider :(


Danke und Grüße
Jacqueline

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Teil von Verweisen durch Tabellenblattname ersetzen
13.11.2023 10:52:11
Jacky151188
Vielleicht gibt es sogar eine Lösung ohne Makro?
AW: Teil von Verweisen durch Tabellenblattname ersetzen
13.11.2023 12:34:30
Yal
Hallo Jacqueline,

eigentlich wäre es besser diese 400 Dateien anhand von Power Query (erst ab xl 2013) oder VBA anzuzapfen und pro Unternehmen eine einzige Zeile als Ergebnis haben. Du hättest dann nur ein Blatt mit 400 Zeilen. Das würde den Aufwand sparen, 400 Dateien zu öffnen und ins Sammeldatei zu kopieren.

Höchsten fügst Du per VBA eine Formel, die direkt auf die Quelldatei verweist, sodass diese geöffnet werden kann, ohne mühsam gesucht zu werden.

Wenn ich es richtig verstehe, in alle Dateien "Unternhemen_xy.xlsx" gibt es ein Blatt "Tabelle1" mit darin von B1 bis Bx die Antwort zu den Fragen 1 bis x stehen.

Also 
für jede Unternehmen-Datei im Verzeichnis "abc"

    Unternehmen-Datei öffnen

    Bereich B1:Bx kopieren

    im Sammelblatt in neue Zeile 

        ab Spalte B transponiert und als wert einfügen

        in Spalte A den Pfad der Unternehmen-Datei als Link ablegen

    Unternehmen-Datei schliessen

nächste Unternehmen-Datei


VG
Yal

Anzeige
Offtopic - anderes Thema, aber wichtig
13.11.2023 16:04:54
Oberschlumpf
Hi Jacqueline,

du, bzw ihr in der Firma arbeitet wirklich noch mit MS Office 2007?????

Dann solltest du oder irgendjemand eurem Chef mal diesen Link zeigen:

https://www.microsoft.com/de-de/microsoft-365/end-of-support-office-2007?market=de

Ich würd sagen: euer Chef spart am falschen Ende - vor allem dann, wenn "euer MS Office 2007" sich mal n Virus einfängt!

Ciao
Thorsten
AW: Offtopic - anderes Thema, aber wichtig
13.11.2023 16:08:31
Jacky151188
nee haben MS 365...Entschuldigt
AW: Offtopic - anderes Thema, aber wichtig
13.11.2023 17:09:42
Yal
Hallo Jacky,

na wunderbar. Dann kann man eine Lösung mit Power Query anstreben.
Voraussetzung ist, alle Dateien liegen in einem Verzeichnis und keine andere Exceldateien leigen darin, alle Dateien haben genau dieselbe Struktur (habe wir schon gescheckt).

Siehe Punkt 5 in https://excelhero.de/power-query/power-query-ganz-einfach-erklaert
aber Videos 1 bis 4 wirst Du als Voraussetzung brauchen (insg. weniger als 1 Std von sehr sinnvoll investierten Zeit)

Zu deiner Aussage: "das hilft mir leider nicht weiter. Ich muss mehrerer solcher Dateien erstellen, jedes Jahr kommen Unternehmen dazu, nächstes Jahr wären es dann schon 500 Quelldateien, die ich kopieren muss...": es war vielleicht nicht eindeutig erkennbar, dass was ich beschrieben habe, keine Anweisung von ein Mensch, sondern für ein Computer (VBA). Damit würde der Rechner (nicht Du) die 500 Dateien selbständig in ein paar Minuten durchlaufen. Passt nicht? Code anpassen, nochmal laufen lassen.
500 Dateien mal 40 Sekunden pro Datei sind es 333 Minuten, also 5 Std 33.

VG
Yal
Anzeige
AW: Offtopic - anderes Thema, aber wichtig
14.11.2023 08:21:47
Jacky151188
Okay, ich muss dann noch etwas an der Verzeichnisstruktur arbeiten, weil momentan neue und alte Versionen drin liegen die unterschiedlich benannt sind. Aber das ist ja schnell gemacht.

Vielen dank für den Tipp mit Power Query, werde ich anwenden :)

Danke und Grüße
AW: Teil von Verweisen durch Tabellenblattname ersetzen
13.11.2023 15:32:31
Jacky151188
Hallo Yal,

erst einmal vielen dank, aber das hilft mir leider nicht weiter. Ich muss mehrerer solcher Dateien erstellen, jedes Jahr kommen Unternehmen dazu, nächstes Jahr wären es dann schon 500 Quelldateien, die ich kopieren muss..

Aber du hast Recht, in allen 400 Dateien "Unternhemen_xxx.xlsx" gibt es ein Blatt "Tabelle1" , darin stehen von B1 bis Bx die Antwort zu den Fragen 1 bis x.

Die Formeln in der Beispieldatei finden die Werte, nur ist der Aufwand sehr groß, alle Formeln in jeden Tabellenblatt zu ersetze.

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige