Wie kann ich einzelne Zellen aus mehreren Exceldateien in einer Exceldatei zusammenfassen?
Liebe Forumsmitglieder.
Ich bin ein Einsteiger in VBA-Makro-Programmierung und habe eine Aufgabe von meinem Chef bekommen:
Daten aus mit VBA-Makro generierten Dateien der jeweiligen Mitarbeiter in einer Datei zusammenführen.
Vorhandene SW: WIN XP Prof. und Office 2003
Problem:
Daten aus mehreren Zellen auslesen und in eine neue Datei eintragen.
Vorhanden:
1 Ordner (c:\Wochenbericht\Team) bei der Assistentin mit mehreren Dateien mit je nur 1 Tabelle. Format: vorname name_KWxx.xls mit gleichem Aufbau
1 Enddatei mit Namen Uebersicht-Wochenbericht.xls
Folgende Zellen sollen transferiert werden:
C3 in A7
C5 in B7
E56 in C7
E55 in D7
I55 in E7
Wer kann mir einen Rat geben, wie ich dies hinbekomme?
Vielen Dank im Voraus.
Gruss Andre
Problem:
Daten aus mehreren Zellen auslesen und in eine neue Datei eintragen.
Vorhanden:
1 Ordner (c:\Wochenbericht\Team) bei der Assistentin mit mehreren Dateien mit je nur 1 Tabelle. Format: vorname name_KWxx.xls mit gleichem Aufbau
1 Enddatei mit Namen Uebersicht-Wochenbericht.xls
Folgende Zellen sollen transferiert werden:
C3 in A7
C5 in B7
E56 in C7
E55 in D7
I55 in E7
Wer kann mir einen Rat geben, wie ich dies hinbekomme?
Vielen Dank im Voraus.
Gruss Andre
Please also mark the comments that contributed to the solution of the article
Content-Key: 158196
Url: https://administrator.de/contentid/158196
Printed on: April 26, 2024 at 05:04 o'clock
16 Comments
Latest comment
Hallo Andre!
Aja, habe ich das jetzt richtig verstanden? Alle *KWxx-Dateien auf einen Schlag kreuz und quer einlesen und die Zell-Inhalte aus C3, C5, E56, E55, I55 in der Übersicht-Tabelle in die Zellen A7:E7 einfügen?????
Gruß Dieter
Aja, habe ich das jetzt richtig verstanden? Alle *KWxx-Dateien auf einen Schlag kreuz und quer einlesen und die Zell-Inhalte aus C3, C5, E56, E55, I55 in der Übersicht-Tabelle in die Zellen A7:E7 einfügen?????
Gruß Dieter
Moin langer0801,
willkommen im Forum.
bitte frag vor irgendeiner handwerklichen Umsetzung deinen Chef noch mal zu dem "ganzen Plan".
Es kann eigentlich nicht so gewesen sein, dass irgendjemand sich die Mühe gemacht hat, liebevoll die Einzel-MA-KWxx-Dateien zu layouten, einzubunten, an -zig Teams zu verteilen und einsammeln zu lassen -- und dann, wenn dieser organisatorische Ablauf nach Monaten endlich etabliert ist, sich mal Gedanken darüber zu machen, wie diese Daten aggregiert/zusammengefasst werden könnten.
Egal was du da jetzt zauberst bzw zaubern lässt wird immer aussehen wie drangepappt und nienich auch nur halbwegs dokumentierbar sein.
Anyway - so wie die Aufgabe von dir verstanden wurde kann sie der Chef eigentlich nicht ernsthaft gestellt haben.
Grüße
Biber
[OT] @76109
Kleiner Tipp: bei dieser Anforderung lässt sich eine Menge Performance tunen, wenn das Makro dann einfach nur eine KWxx-Datei einliest. Macht doch inhaltlich keinen Unterschied.
[/OT]
willkommen im Forum.
bitte frag vor irgendeiner handwerklichen Umsetzung deinen Chef noch mal zu dem "ganzen Plan".
Es kann eigentlich nicht so gewesen sein, dass irgendjemand sich die Mühe gemacht hat, liebevoll die Einzel-MA-KWxx-Dateien zu layouten, einzubunten, an -zig Teams zu verteilen und einsammeln zu lassen -- und dann, wenn dieser organisatorische Ablauf nach Monaten endlich etabliert ist, sich mal Gedanken darüber zu machen, wie diese Daten aggregiert/zusammengefasst werden könnten.
Egal was du da jetzt zauberst bzw zaubern lässt wird immer aussehen wie drangepappt und nienich auch nur halbwegs dokumentierbar sein.
Anyway - so wie die Aufgabe von dir verstanden wurde kann sie der Chef eigentlich nicht ernsthaft gestellt haben.
Grüße
Biber
[OT] @76109
Kleiner Tipp: bei dieser Anforderung lässt sich eine Menge Performance tunen, wenn das Makro dann einfach nur eine KWxx-Datei einliest. Macht doch inhaltlich keinen Unterschied.
[/OT]
Hallo Biber!
Ja, genau das war mein Plan
[/OT]
Gruß Dieter
Zitat von @Biber:
[OT] @76109
Kleiner Tipp: bei dieser Anforderung lässt sich eine Menge Performance tunen, wenn das Makro dann einfach nur
eine KWxx-Datei einliest. Macht doch inhaltlich keinen Unterschied.
[/OT]
[OT][OT] @76109
Kleiner Tipp: bei dieser Anforderung lässt sich eine Menge Performance tunen, wenn das Makro dann einfach nur
eine KWxx-Datei einliest. Macht doch inhaltlich keinen Unterschied.
[/OT]
Ja, genau das war mein Plan
[/OT]
Gruß Dieter
Die Zellwerte aus anderen Dateien zu übernehmen ist eigentlich nicht schwer, es wird nur bei sehr vielen Zellen recht mühsam. So funktioniert es:
1) Alle Excel-Dateien öffnen, die benötigt werden
2) In der Ziel-Excel-Datei in der gewünschten Zelle ein "=" eingeben (also ohne die Hochkommata)
3) Nun mit der Maus die andere Exceldatei anklicken und dort die Zelle, die übernommen werden soll
4) Fertig: in der ursprünglichen Excel-Datei, in der das "=" eingegeben wurde, steht nun die Formel, die notwendig ist, damit der Wert aus der anderen Excel-Datei angezeigt wird
5) Diesen Vorgang nun für jede gewünschte Zelle wiederholen...
1) Alle Excel-Dateien öffnen, die benötigt werden
2) In der Ziel-Excel-Datei in der gewünschten Zelle ein "=" eingeben (also ohne die Hochkommata)
3) Nun mit der Maus die andere Exceldatei anklicken und dort die Zelle, die übernommen werden soll
4) Fertig: in der ursprünglichen Excel-Datei, in der das "=" eingegeben wurde, steht nun die Formel, die notwendig ist, damit der Wert aus der anderen Excel-Datei angezeigt wird
5) Diesen Vorgang nun für jede gewünschte Zelle wiederholen...
Hallo Andre!
Erstmal ein dickes Lob, dass Du Dir die Mühe gemacht hast, Beispieldateien zum Download bereitzustellen
War auch insofern gut, weil dadurch auch gleich das Problem mit den Verknüpfungen der exportierten Kalenderwoche-Dateien für den EMail-Versand aufgefallen ist. Das Problem ist, dass die Namen in den KW-Sheets per Verknüpfung aus der Übersicht generiert werden und in der Export-Datei nicht mehr Verfügbar sind. Von daher sind noch Extramaßnahmen erforderlich.
Zum Ablauf:
Beim Makro-Start werden die *_KW#-Dateien immer komplett neu eingelesen, das hat u.a. auch den Vorteil, dass falls mal von einem Mitarbeiter, aus welchen Gründen auch immer, eine Datei fehlt, diese zu einem anderen Zeitpunkt problemlos erfasst werden kann. Desweiteren kann die Übersicht-Wochenbericht einfach für das Folgejahr kopiert werden. Nach dem Einlesen aller *_KW#-Dateien, werden diese erst nach KW und anschließend nach Namen sortiert, wobei noch zu überlegen wäre, ob in den *_KW#-Dateien-Übersichts-Sheets nicht die Reihenfolge des Namens geändert werden soll z.B. anstatt "André Testman" erst den Nachnamen "Testman, André, sowas in der Art. Ansonsten wird nach Vorname sortiert. Nach Beendigung des Einlesens, wird die Arbeitsmappe automatisch gesichert und eine Meldung ausgegeben.
Hier mal der Code zum testen (Pfad-Konstante entsprechend anpassen):
Gruß Dieter
Erstmal ein dickes Lob, dass Du Dir die Mühe gemacht hast, Beispieldateien zum Download bereitzustellen
War auch insofern gut, weil dadurch auch gleich das Problem mit den Verknüpfungen der exportierten Kalenderwoche-Dateien für den EMail-Versand aufgefallen ist. Das Problem ist, dass die Namen in den KW-Sheets per Verknüpfung aus der Übersicht generiert werden und in der Export-Datei nicht mehr Verfügbar sind. Von daher sind noch Extramaßnahmen erforderlich.
Zum Ablauf:
Beim Makro-Start werden die *_KW#-Dateien immer komplett neu eingelesen, das hat u.a. auch den Vorteil, dass falls mal von einem Mitarbeiter, aus welchen Gründen auch immer, eine Datei fehlt, diese zu einem anderen Zeitpunkt problemlos erfasst werden kann. Desweiteren kann die Übersicht-Wochenbericht einfach für das Folgejahr kopiert werden. Nach dem Einlesen aller *_KW#-Dateien, werden diese erst nach KW und anschließend nach Namen sortiert, wobei noch zu überlegen wäre, ob in den *_KW#-Dateien-Übersichts-Sheets nicht die Reihenfolge des Namens geändert werden soll z.B. anstatt "André Testman" erst den Nachnamen "Testman, André, sowas in der Art. Ansonsten wird nach Vorname sortiert. Nach Beendigung des Einlesens, wird die Arbeitsmappe automatisch gesichert und eine Meldung ausgegeben.
Hier mal der Code zum testen (Pfad-Konstante entsprechend anpassen):
Option Explicit
Option Compare Text
Const Folder = "C:\Wochenbericht\Team"
Const StartZeile = 7
Sub GetWeekData()
Dim Wkb As Workbook, Fso As Object, File As Object, Zeile As Long
Set Fso = CreateObject("Scripting.FileSystemObject") 'Dateisystem-Operationen
Range(Cells(StartZeile, "A"), Cells(Rows.Count, "E")).ClearContents 'Inhalte ab Startzeile löschen
Zeile = StartZeile
With Application
.ScreenUpdating = False 'Bildschirmaktualisierung aus
.AskToUpdateLinks = False 'Verknüpfung (Name aus Übersicht) ohne Abfrage aktualisieren
.DisplayAlerts = False 'Fehlermeldung "Verknüpfung kann nicht..." unterdrücken
End With
For Each File In Fso.GetFolder(Folder).Files 'Alle _KW#.xls-Dateien einlesen und eintragen
If Fso.GetExtensionName(File.Name) Like "xls" And Fso.GetBaseName(File.Name) Like "*_KW#*" Then
Set Wkb = GetObject(File.Path)
With Wkb.Sheets(1) 'Werte mit Zahlenformat
.Range("C3").Copy: Cells(Zeile, "A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("C5").Copy: Cells(Zeile, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("E56").Copy: Cells(Zeile, "C").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("E55").Copy: Cells(Zeile, "D").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("I55").Copy: Cells(Zeile, "E").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Werte ohne Zahlenformat
'Cells(Zeile, "A") = .Range("C3")
'Cells(Zeile, "B") = .Range("C5")
'Cells(Zeile, "C") = .Range("E56")
'Cells(Zeile, "D") = .Range("E55")
'Cells(Zeile, "E") = .Range("I55")
End With
Wkb.Close False: Zeile = Zeile + 1
End If
Next
Range(Cells(StartZeile, "A"), Cells(Zeile, "E")).Sort _
Key1:=Cells(StartZeile, "B"), Key2:=Cells(StartZeile, "A"), Header:=xlNo, Orientation:=xlTopToBottom
With Application
.DisplayAlerts = True
.AskToUpdateLinks = True
.ScreenUpdating = True
End With
ThisWorkbook.Save: MsgBox "Fertig", vbInformation, "Wochenberichte einlesen..."
End Sub
Gruß Dieter
Moin didi1954,
halte ich -mit Verlaub gesagt- für Grütze.
@langer0801
WTF mailen deine Jungs & Mädels denn jedesmal eine Komplett-Kopie ihrer Excel-Datei statt einfach eine CSV-Datei mit einer gatesverdammten Zeile mit ihrem Namen/Personalnummer, Kalenderwoche und den dreieinhalb Zahlenwerten?
Es kann doch nicht dein Plan gewesen sein, dass jede Woche wieder diese Komplett-Jahres-Excel-Dateien mit 53 Wochen hin und her gemüllt werden, wenn davon genau eine KW relevant ist?
Und wer stellt sicher, dass von "Andre Testman" nicht 12 verschiedene XLS-Dateien im Wochenberichtsordner sind.
--> All in all: Ich halte das Konzept NICHT für so ausgereift, dass ich zu diesem Zeitpunkt Makros über den vorhandenen Datenwust schrapeln lassen würde.
Man/frau könnte doch mal auf einen Bierdeckel malen:
Grüße
Biber
halte ich -mit Verlaub gesagt- für Grütze.
- egal, ob die Jungs und Mädels ihre KW1 oder KW43 rübermailen; du holst die Daten immer aus dem KW1-Sheet (WkbSheet(1)).
- selbst wenn du nun über tolldreiste Bande ("Hole aus dem Dateinamen die Info, welche KW ich lesen muss") das "richtige" KW-Datenblatt einsaugst: WTF??? Du holst die Daten aus den Detail-Sheets, obwohl die dreieinhalb Kasperwerte schon in dem "Übersichtsblatt" zusammengebraten sind?
- Du holst den Euro-Wert aus "KW1!I55", was eine verbundene Zelle ist, was zu dem "Copy-Cell-Fehler" führt und in Folge dazu, dass du bei einem Import nun Daten und Formate übernehmen musst --> das kann doch nicht die Lösung sein.
@langer0801
WTF mailen deine Jungs & Mädels denn jedesmal eine Komplett-Kopie ihrer Excel-Datei statt einfach eine CSV-Datei mit einer gatesverdammten Zeile mit ihrem Namen/Personalnummer, Kalenderwoche und den dreieinhalb Zahlenwerten?
Es kann doch nicht dein Plan gewesen sein, dass jede Woche wieder diese Komplett-Jahres-Excel-Dateien mit 53 Wochen hin und her gemüllt werden, wenn davon genau eine KW relevant ist?
Und wer stellt sicher, dass von "Andre Testman" nicht 12 verschiedene XLS-Dateien im Wochenberichtsordner sind.
--> All in all: Ich halte das Konzept NICHT für so ausgereift, dass ich zu diesem Zeitpunkt Makros über den vorhandenen Datenwust schrapeln lassen würde.
Man/frau könnte doch mal auf einen Bierdeckel malen:
- welche (Detail-)Informationen sind für die Detail-ErfasserInnen sinnvoll?
- welche Daten brauche ich in einer abgehobenen Zusammenfassung?
- welche Daten müssen dementsprechend von den Indianern an des Häuptlings Sekretärin gemailt werden?
Grüße
Biber
Hallo Biber!
Ja stimmt, ich hole die Daten immer aus Sheet(1), was je nach KW eine andere KW enthält. Der Mitarbeiter kopiert wöchentlich aus seiner Arbeitsmappe das aktuelle KW-Sheet (MitarbeiterName_KW#*) in eine seperate Arbeitsmappe zum Versand (den Code nicht angeschaut?). D.h. am Jahresende befinden sich von jedem Mitarbeiter 52 KW-Dateien a 1 Sheet im Team-Ordner.
Aus dem Dateinamen brauche ich keine KW-Info aus dem Dateinamen zu kreieren, da diese im Sheet steht und nur dem Hinweis dient, das es eine Datei ist, die eingelesen werden soll, für den Fall, dass sich noch andere *.xls-Dateien in dem Ordner befinden z.B. Die "Uebersicht_Wochenbericht.xls"...
Die Übersicht steht in der Versand-Mappe erstens nicht zur Verfügung und zweitens, wie stelle ich dann fest, welche KW die aktuelle ist... ?
Und was die Daten angeht, reicht es auch, wenn die Zellen einmal in der Übersicht vorformatiert werden und nur die Werte eingetragen werden. Von daher lösche ich beim Neu- Einlesen auch nur die Inhalte und nicht die Formate.
Die Möglichkeiten sind nun mal unbegrenzt und eine davon habe ich anhand der gegebenen Umstände angeboten und wenn's Grütze ist, naja, es besteht ja kein Verwendungszwang! Zumindest funktioniert der Grütze-Code
Gruß Dieter
PS. Heute mit dem linken Fuß zuerst aufgestanden?
Ja stimmt, ich hole die Daten immer aus Sheet(1), was je nach KW eine andere KW enthält. Der Mitarbeiter kopiert wöchentlich aus seiner Arbeitsmappe das aktuelle KW-Sheet (MitarbeiterName_KW#*) in eine seperate Arbeitsmappe zum Versand (den Code nicht angeschaut?). D.h. am Jahresende befinden sich von jedem Mitarbeiter 52 KW-Dateien a 1 Sheet im Team-Ordner.
Aus dem Dateinamen brauche ich keine KW-Info aus dem Dateinamen zu kreieren, da diese im Sheet steht und nur dem Hinweis dient, das es eine Datei ist, die eingelesen werden soll, für den Fall, dass sich noch andere *.xls-Dateien in dem Ordner befinden z.B. Die "Uebersicht_Wochenbericht.xls"...
Die Übersicht steht in der Versand-Mappe erstens nicht zur Verfügung und zweitens, wie stelle ich dann fest, welche KW die aktuelle ist... ?
Und was die Daten angeht, reicht es auch, wenn die Zellen einmal in der Übersicht vorformatiert werden und nur die Werte eingetragen werden. Von daher lösche ich beim Neu- Einlesen auch nur die Inhalte und nicht die Formate.
Die Möglichkeiten sind nun mal unbegrenzt und eine davon habe ich anhand der gegebenen Umstände angeboten und wenn's Grütze ist, naja, es besteht ja kein Verwendungszwang! Zumindest funktioniert der Grütze-Code
Gruß Dieter
PS. Heute mit dem linken Fuß zuerst aufgestanden?
Hallo Andre!
Freut mich
Mit dieser Nachricht, hast Du mir den Tag auch noch gerettet, nachdem mir Biber eine heftige Breitseite verpasst hat
Gruß Dieter
Freut mich
Mit dieser Nachricht, hast Du mir den Tag auch noch gerettet, nachdem mir Biber eine heftige Breitseite verpasst hat
Gruß Dieter
Hallo Andre!
Yepp, gern geschehen und auch noch einen schönen Extra-Gruß von mir an Deine Frau
Gruß Dieter
Yepp, gern geschehen und auch noch einen schönen Extra-Gruß von mir an Deine Frau
Gruß Dieter
Moin didi1954,
sorry, sorry, sorry...
ich hatte in der Tat im Makro "Sub Excel_Sheet_via_Outlook_Senden()" nur das "ActiveWorkbook.SaveAs" wahrgenommen und nicht das "ActiveSheet.Copy" in der Zeile davor - ich war von gemailten Dateien im Format der Datei "Wochenbericht_bla.xls" ausgegangen.
Dann nehme ich (fast) alles zurück - außer der Frage, warum nun dieser Euronen-Betrag ausgerechnet aus einem Verbundene-Zellen-Feld ausgelesen werden muss.
Und ob statt "ActiveSheet.Copy" nicht schon beim Kopie-Erstellen "nur die Daten" statt wie jetzt die Verknüpfungen kopiert werden sollten.
Aber wenn es so funktioniert und organisatorisch handlebar ist... dann bin ich auch still.
Morgen ist ja Montag, aber für Dienstag nehme ich mir vor, wieder mit einem rechteren Fuss aufzustehen.
Versprochen
Biber
sorry, sorry, sorry...
ich hatte in der Tat im Makro "Sub Excel_Sheet_via_Outlook_Senden()" nur das "ActiveWorkbook.SaveAs" wahrgenommen und nicht das "ActiveSheet.Copy" in der Zeile davor - ich war von gemailten Dateien im Format der Datei "Wochenbericht_bla.xls" ausgegangen.
Dann nehme ich (fast) alles zurück - außer der Frage, warum nun dieser Euronen-Betrag ausgerechnet aus einem Verbundene-Zellen-Feld ausgelesen werden muss.
Und ob statt "ActiveSheet.Copy" nicht schon beim Kopie-Erstellen "nur die Daten" statt wie jetzt die Verknüpfungen kopiert werden sollten.
Aber wenn es so funktioniert und organisatorisch handlebar ist... dann bin ich auch still.
Morgen ist ja Montag, aber für Dienstag nehme ich mir vor, wieder mit einem rechteren Fuss aufzustehen.
Versprochen
Biber
Hallo Biber!
Ja, wer lesen kann, ist klar im Vorteil (mein Lieblingsspruch)
Die Euronen-Zelle ist nicht das eigentlich Problem und im Nachhinein, wäre es mit Copy -> nach Ziel eh nicht gegangen, da die Zellen zum Teil auch Formeln enthalten. Die störende Verknüpfung befindet sich eigentlich nur in der Zelle C3 (Name), weil alle Mitarbeiter-KW-Sheets eine Verknüpfung zur Übersicht für den Namen haben und dieses Sheet ja nicht übermittelt wird. Aber Excel macht nunmal seine standardmäßige "Aktalisieren/Nicht aktualisieren"-Abfrage, die sich mit Display-Alerts nicht unterdrücken läßt.
Allerdings hätte man der Einfachheit halber auch wirklich eine Einzeilige-CSV-Datei versenden können, sofern in der Zentrale keine Sheet-Detailansicht nötig ist?. Eventuell kann sich der TO ja noch für dafür entscheiden, doch eine CSV-Datei zu nehmen?
Ansonsten, wünsche ich Dir einen weniger nervaufreibenden Montag
Gruß Die
Ja, wer lesen kann, ist klar im Vorteil (mein Lieblingsspruch)
Die Euronen-Zelle ist nicht das eigentlich Problem und im Nachhinein, wäre es mit Copy -> nach Ziel eh nicht gegangen, da die Zellen zum Teil auch Formeln enthalten. Die störende Verknüpfung befindet sich eigentlich nur in der Zelle C3 (Name), weil alle Mitarbeiter-KW-Sheets eine Verknüpfung zur Übersicht für den Namen haben und dieses Sheet ja nicht übermittelt wird. Aber Excel macht nunmal seine standardmäßige "Aktalisieren/Nicht aktualisieren"-Abfrage, die sich mit Display-Alerts nicht unterdrücken läßt.
Allerdings hätte man der Einfachheit halber auch wirklich eine Einzeilige-CSV-Datei versenden können, sofern in der Zentrale keine Sheet-Detailansicht nötig ist?. Eventuell kann sich der TO ja noch für dafür entscheiden, doch eine CSV-Datei zu nehmen?
Ansonsten, wünsche ich Dir einen weniger nervaufreibenden Montag
Gruß Die