Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Spalten von mehreren Exceldateien in einer Gesamtdatei auflisten (mit autom. aktualisierung der Werte)

Frage Microsoft Microsoft Office

Mitglied: simba2282

simba2282 (Level 1) - Jetzt verbinden

08.02.2007, aktualisiert 13.02.2007, 5393 Aufrufe, 6 Kommentare

Hallo liebe Profis ,

ich habe ein Problem an dem ich schon eine ganze Weile verzweifle. Eine erste Lösung habe ich nun schon in diesem Forum gefunden (siehe Code unten). Bräuchte aber noch ein paar Verfeinerungen...

Das Problem ist folgendes:
Ich habe Timesheetdateien von ca. 30 Mitarbeitern (Excel Datei mit jeweils einem Sheet pro Monate in dem pro Tag und Aufgabe die Stunden eingegeben werden können. Diese werden hier dann schon als Wochenwerte aufsummiert. Ausserdem gibt es ein Dropdownmenue in dem die Tasks ausgewählt werden und die automatisch andere Zellen befüllen). Aus diesen Dateien möchte ich Teile (die Aufgaben und die Wochenwerte) in einer Sammeldatei untereinander aufführen (inklusive der Formatierungen), um eine Übersicht zu haben. Diese sollte sich beim Öffnen immer automatisch aktualisieren, wenn es in den Einzeldateien Änderungen gegeben hat (ich möchte jede Woche die aktualisierten Dateien der Mitarbeiter in dem Sammelsheet ansehen).
Mit dem unten stehenden Code kann ich nun schon die Daten aus den verschiedenen Dateien in einer Auflisten, solange die beiden Sheets genau die gleiche Struktur haben und ich alle Spalten übernehme.

Ich arbeite in den Timesheets mit definierten Namen und Dropdownmenues sowie etlichen Formeln.
Das Problem bei der bisherigen Lösung (Code unten) ist, dass ich immer eine Meldung bekomme, in der ich den Bereichsnamen umbenennen muss, damit die Daten übernommen werden. Kann dies irgendwie umgangen werden?

Und diese Lösung funktioniert auch nur, wenn die zusammenfassende Datei exakt die gleiche Struktur hat wie die einzelnen...
Ich möchte aber ganz gerne nur bestimmte Spalten aus den einzelnen Dateien rüberziehen (Beispiel: in den Einzelsheet können Stunden pro Tag eingegeben werden, die dann wochenweise aufsummiert werden. In der Sammeldatei hätte ich gerne nur die Wochenwerte...)
Und es werden auch aus jeder Datei aus jedem Sheet die Überschriften wieder übernommen, die ich auch gerne rauslassen würde.
Ist das auch irgendwie möglich?

Und das dritte Problem ist, wie gesagt, dass die Daten nicht automatisch aktualisiert werden, wenn ich in den Einzeldateien etwas ändere.
Hier kann ich höchsten jede Woche dann ein neues Sammelsheet machen...

Ich habe leider so gut wie gar keine Ahnung von VBA und wäre für jede Hilfe dankbar.

Ich kann auch gerne Beispiel Dateien schicken, wenn das hilft.

Viele Grüße,
simba2282

gefundener Code/bisherige Lösung:

Option Explicit

Sub Sammle()
Const sSourcePath As String = "D:\Viele viele XLS"
Dim wbGes As Workbook, wsTarget As Worksheet
Dim wbTeil As Workbook, wsSource As Worksheet
Dim fso As Object, oFile As Object
Dim rNext As Integer, bSheetFound As Boolean
Set wbGes = ActiveWorkbook
Set fso = CreateObject("Scripting.FileSystemObject")

'Quell-Ordner durchsuchen
For Each oFile In fso.GetFolder(sSourcePath).Files
'nur .xls-Dateien bearbeiten
If LCase(Right(oFile.Name, 4)) = ".xls" Then
Application.Workbooks.Open (oFile.Path)
Set wbTeil = ActiveWorkbook
'alle Tabellen der Gesamt-Datei bearbeiten
For Each wsTarget In wbGes.Worksheets
'Tabelle auch in Teil-Datei enthalten?
For Each wsSource In wbTeil.Worksheets
bSheetFound = False
If LCase(wsTarget.Name) = LCase(wsSource.Name) Then
bSheetFound = True
Exit For
End If
Next
If bSheetFound Then
'Tabelle da, Daten kopieren ...
wbTeil.Worksheets(wsTarget.Name).Activate
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
'... und einfügen in Gesamt-Datei-Tabelle...
wbGes.Worksheets(wsTarget.Name).Activate
'... ab der nächsten freien Zeile
rNext = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
ActiveSheet.Cells(rNext, 1).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next 'Tabelle
wbTeil.Close
End If
Next 'Datei
wbGes.Worksheets(1).Activate
'Gesamt-Datei speichern
wbGes.Save
MsgBox "Fertig."
End Sub
Mitglied: bastla
08.02.2007 um 11:31 Uhr
Hallo simba2282!

Vielleicht siehst Du Dir einmal im Menü Daten den Befehl "Konsolidieren" an - wenn Deine Einzeldateien genug Gemeinsamkeiten im Dateinamen (und einen gemeinsamen Pfad) haben, könntest Du als "Verweis" zB angeben:
01.
'[Timesheet-07-02-*]Tabelle1'!Wochensumme
Ansonsten müsstest Du jede Datei einzeln als "Verweis" einfügen. Durch "Verknüpfung mit Quelldaten" werden dann auch Änderungen automatisch übernommen.

Grüße
bastla
Bitte warten ..
Mitglied: simba2282
08.02.2007 um 13:56 Uhr
Hallo,

das habe ich auch schon probiert aber leider geht das nicht so wirklich... Vielleicht mache ich da was falsch? Aber ich glaub das geht vom Aufbau der Einzeltimesheets schon nicht...
Ich versuch mal zu skizzieren, wie es aussieht

Einzeltimesheet:
Tabellenblatt 1 (Jan 07):

Person|WP|Aufgabe|Allowance|to date|Summe Monat||2.1|3.1|4.1|5.1|Sum KW1|ETC KW1|8.1|9.1|10.1|11.1|12.1|Sum KW2|ETC KW2|...
P1 | 1 |waschen| 15 | 3 | 10 || 1 | 0 | 2 | 0 | 3 | 2 | 1 | 0 | 0 | 0 | 1 | 2 | 0 |...

Das ganze gibt es dann auf zwölf Tabellenblättern (für jeden Monat eins) mit mehreren Zeilen. Und dann diese Datei von 30 Leuten.
"SUmme Monat", "Sum KW* und ETC KW*" werden berechnet, die Aufgabe ist mittels Dropdownmenue auszuwählen (es wurde also ein Bereichsname vergeben), und die Spalten "Allowance" und "to date" werden mittels SVerweis aus dem vorigen Tabellenblatt gezogen.

Die Zielsammeldatei soll dann folgendermaßen aussehen:

Tabellenblatt 1 (Jan 07):

Person|WP|Aufgabe|Allowance|to date|Summe Monat||Sum KW1|ETC KW1|Sum KW2|ETC KW2|...
P1 | 1 |waschen| 15 | 3 | 10 || 3 | 2 | 2 | 0 |...
| ... | shoppen| 30 | 1 | ... || ... | .. | ... | ... | ...

P2 | 1 |bügeln | 20 | 3 | 10 || 3 | 2 | 4 | 0 |...
| ... | ... | ... | ... | ... || ... | .. | ... | ... | ...

...
...

und das ganze dann auch wieder für jeden Monat ein Tabellenblatt in dem die entsprechenden Einträge der Einzelsheets untereinander aufgelistet werden...

und das sich automatisch aktualisiert..


Weiss jemand weiter?
Bitte warten ..
Mitglied: bastla
08.02.2007 um 16:09 Uhr
Hallo simba2282!

Sofern ich Dich inzwischen richtig verstehe, sollen in die Zusammenfassung nur bestehende Werte / Ergebnisse (also ohne zusätzliche Berechnungen) der Einzeldateien übernommen werden, wobei die Zeilenanzahl in den Einzeldateien nicht fest steht (und durch die Wochengliederung auch die Spaltenstruktur in den einzelnen Monaten abweicht).

Unter diesen Voraussetzungen könntest Du folgende Vorgangsweise (ganz ohne VBA) versuchen:
  • In Zeile 1 der Sammeldatei die Überschriften erfassen (kopieren) und in A2 folgende Formel eingeben:
01.
='[Datei1.xls]Jan 07'!A2
  • Diese Formel durch Ausfüllen nach rechts zunächst für alle Spalten kopieren (wenn es einige Spalten mehr sind, macht's auch nix).
  • Die so entstandene Zeile 2 nach unten für die zu erwartende Anzahl von Einträgen (+ Sicherheitszuschlag) kopieren - damit kannst Du schon einmal sämtliche "Jan 07"-Daten der "Datei1.xls" sehen.
  • Direkt anschließend dasselbe für die Dateien 2, 3, ..., 30. Zwischen den Datenblöcken dürfen sich keine Leerzeilen befinden!
  • (Zwischendurch etwas für die Optik / Übersicht: "Extras/Optionen/Ansicht/Nullwerte")
  • Über "Daten/Filter/Autofilter" und dann zB in der Spalte "Person" per Auswahl "(Nichtleere)" kannst Du eine entsprechend kompakte Darstellung erreichen und Du hättest es eigentlich schon fast geschafft (nein, ich habe die Spaltenauswahl nicht vergessen ).
  • Jetzt noch das "Jan 07"-Zusammenfassungsblatt für die anderen Monate kopieren und in den anderen Blättern jeweils nach dem Markieren mit Strg-A in allen Formeln mit Strg-H "Jan" gegen "Feb", ... austauschen.
  • Die Spalten könntest Du jetzt noch in den einzelnen Monatsblättern so verschieben, dass die nicht benötigten Tageswerte nach rechts wandern (oder diese ausblenden oder auch gleich ganz löschen). Als Verschiebetechnik bietet sich nach dem Markieren der gesamten Spalte das Ziehen am Rand der Markierung (nicht am Ausfüllkästchen) mit der rechten Maustaste mit anschließender Auswahl von "Ausschneiden und nach rechts verschieben" an.

Grüße
bastla
Bitte warten ..
Mitglied: simba2282
13.02.2007 um 12:27 Uhr
Hallo bastla,

vielen Dank für die Antwort, das sieht schon mal ganz gut aus und ganz ohne Code

Das einzige Problem, dass ich da jetzt noch habe ist, dass er die Formatierungen aus den Einzelsheets nicht mit übernimmt. Ich habe bedingte Formatierungen eingebaut, die bei bestimmten Bedingungen Zellen rot färbt und diese Färbungen wären in der Übersicht wichtig zu sehen.

Gibt es da auch eine Möglichkeit diese Formatierungen mit zu übernehmen?

Vielen, vielen Dank für deine Hilfe!!

Gruß Simba2282
Bitte warten ..
Mitglied: bastla
13.02.2007 um 12:39 Uhr
Hallo simba2282!

Eigentlich dürfte es wenig Arbeit sein, die entsprechenden Formate einmalig von Hand zu übertragen (natürlich noch vor dem Kopieren) - ansonsten sehe ich ohne Programmierung eigentlich keine Möglichkeit, zu den Formaten zu kommen.

Grüße
bastla
Bitte warten ..
Mitglied: simba2282
13.02.2007 um 14:53 Uhr
Hallo bastla,

stimmt, das ist wirklich kein Aufwand. Super, dann hab ich es jetzt genauso wie ich es haben wollte!!
Vielen, vielen Dank für deine Antworten, hast mir sehr geholfen!!

Viele Grüße,
simba2282
Bitte warten ..
Neuester Wissensbeitrag
Ähnliche Inhalte
LAN, WAN, Wireless
gelöst 1 Port in mehreren VLANs? (7)

Frage von mario87 zum Thema LAN, WAN, Wireless ...

VB for Applications
gelöst Bestimmte Spalten aus CSV-Datei auslesen (VBS) (9)

Frage von Gurkenhobel zum Thema VB for Applications ...

Heiß diskutierte Inhalte
Windows Userverwaltung
Ausgeschiedene Mitarbeiter im Unternehmen - was tun mit den AD Konten? (33)

Frage von patz223 zum Thema Windows Userverwaltung ...

LAN, WAN, Wireless
FritzBox, zwei Server, verschiedene Netze (21)

Frage von DavidGl zum Thema LAN, WAN, Wireless ...

Viren und Trojaner
Aufgepasst: Neue Ransomware Goldeneye verbreitet sich rasant (20)

Link von Penny.Cilin zum Thema Viren und Trojaner ...

Windows Netzwerk
Windows 10 RDP geht nicht (18)

Frage von Fiasko zum Thema Windows Netzwerk ...