Top-Themen

Aktuelle Themen (A bis Z)

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, 5418 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 ..
Ähnliche Inhalte
Microsoft Office
Wert aus einer Spalte mit Werten in einer Spalten vergleiche, wenn Wert vorhanden, dann Wert aus der dritten Spalte in der selben Zeile ausgeben, verschiedene Tabellenblätter
gelöst Frage von DevilioMicrosoft Office5 Kommentare

Hallo Zusammen, ich habe folgendes Problem und finde keine Lösung. Ich habe 2 Tabellenblätter mit folgenden Daten: Tabelle 1: ...

Datenbanken
Update auf eine Spalte in Zeilen, die den gleichen wert in mehreren anderen Spalten enthalten
Frage von rohannDatenbanken5 Kommentare

Hallo Leute! Vermutlich ein leichter Griff für geübte SQLer, aber ich komme gerade nicht richtig weitervielleicht ist das so ...

Microsoft Office
Werte in Spalten vergleichen und einsetzen
gelöst Frage von Inge88Microsoft Office4 Kommentare

Halihalo, ich bin neu hier und versuche eine Lösung zu finden, bzw. kompetente Excel-Kenner: Ich habe mehrere Spalten (A, ...

PHP
Werte in einer sql Spalte addieren
gelöst Frage von helmuthelmut2000PHP13 Kommentare

Hallo, Ich habe folgendes Problem. Ich habe eine PHP Seite mit einer MSSQL Datenbank. Da gibt es eine Tabelle ...

Neue Wissensbeiträge
Apple

IOS 11.2.1 stopft HomeKit-Remote-Lücke

Tipp von BassFishFox vor 6 StundenApple

Das Update für iPhone, iPad und Apple TV soll die Fernsteuerung von Smart-Home-Geräten wieder in vollem Umfang ermöglichen. Apple ...

Windows 10

Windows 10 v1709 EN murkst bei den Regionseinstellungen

Tipp von DerWoWusste vor 11 StundenWindows 10

Dieser kurze Tipp richtet sich an den kleinen Personenkreis, der Win10 v1709 EN-US frisch installiert und dabei die englische ...

Webbrowser

Kein Ton bei Firefox Quantum über RDP

Tipp von Moddry vor 11 StundenWebbrowser

Hallo Kollegen! Hatte das Problem, dass der neue Firefox bei mir auf der Kiste keinen Ton hat, wenn ich ...

Internet

EU-DSGVO: WHOIS soll weniger Informationen liefern

Information von sabines vor 23 StundenInternet4 Kommentare

Wegen der europäische Datenschutzgrundverordnung stehen die Prozesse um die Registrierung von Domains auf dem Prüfstand. Sollte die Forderungen umgesetzt ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
Von rj11 auf rj45
Frage von jensgebkenLAN, WAN, Wireless19 Kommentare

Hallo Gemeinschaft, könnt ihr mir vielleicht bei der anfertigung eines Kabels helfen - habe ein rj 11 stecker und ...

Netzwerkmanagement
Firefox Profieles im Roaming
gelöst Frage von Hendrik2586Netzwerkmanagement17 Kommentare

Hallo liebe Leute. :) Ich hab da ein kleines Problem, welches anscheinend nicht unbekannt ist. Wir nutzen hier in ...

LAN, WAN, Wireless
Häufig Probleme beim Anmelden in WLAN
Frage von mabue88LAN, WAN, Wireless15 Kommentare

Hallo zusammen, in einem Netzwerk gibt es relativ häufig (1-2 mal pro Woche) Probleme mit der WLAN-Verbindung. Zunächst mal ...

Windows Server
Remotesteuerung der Sitzung (Kennung XX) fehlgeschlagen
gelöst Frage von Stefan91Windows Server14 Kommentare

Hallo Zusammen, seit kurzem bekomme ich oben genannte Fehlermeldung, wenn ich versuche eine Remotesitzung über den Taskmanager fernzusteuern (Rechtsklick ...