15.07.2009, aktualisiert um 10:08:12 Uhr

6225

Wert fester Zellen verschiedener Dateien auslesen

Hallo,

ich bin was Excel - und insb. was VBA angeht - ein absoluter Beginner und hoffe, bei Euch etwas Hilfe zu bekommen. Hier ist mein Problem:

Es geht um eine Zusammenfassung von Arbeitszeiterfassung. Für jeden Monat gibt es ein Excel Workbook. Für jeden Mitarbeiter innerhalb des Workbooks ein Sheet, auf welchem die genauen Zeiten als auch die Gesamtsumme erfasst sind. Alle Workbooks befinden sich in einem Ordner.

Ich möchte nun eine Übersichtstabelle erstellen, die sich aus allen Sheets/Workbooks die entsprechenden Daten automatisch zieht:

Abteilung = Zelle A4
Name: Zelle A6
Gesamtstunden: Zelle S41

Ich hoffe, das reicht erstmal an Information. Vielen Dank im Voraus für Eure Hilfe!

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 120493

Url: https://administrator.de/contentid/120493

Ausgedruckt am: 23.11.2024 um 02:11 Uhr

23 Kommentare

Neuester Kommentar

hast du dabei an ein Makro gedacht?

Hallo Maburaho21,

Ja ich denke das ist die beste Möglichkeit. Allerdings weiss ich eben nicht genau, wie man das Makro programmiert.

Danke!!

hast du schon mal ein excel-makro programmiert?

Hallo jeanpaul23!

Ein paar Info's fehlen noch.

Befinden sich in dem Ordner außer der Übersicht- und Monatsdateien noch andere Dateien?

Wie heißt die Übersichtsdatei?

Wie heißt das Tabellenblatt?

Und wie soll die Übersicht aussehen? Spalte A = Abteilung, Spalte B = Name, Spalte C = Stunden?

Gruß Dieter

Hallo Dieter,

nein, ausser der "Ziel-Datei" und den Monatsdateien befindet sich in dem Ordner nichts.

Die Übersichtsdatei nennen wir einfach mal "target.xls".
Das Tabellenblatt in der Übersichtsdatei heisst "YTD JUNE 09"

In den Monatsdateien befindet sich immer ein Summary Sheet vor allen Mitarbeiter-Sheets. Dieses Summary Sheet soll nicht einbezogen werden.

Die Übersichtsdatei "target.xls" soll so aussehen:

Spalte A: leer
Spalte B: Monat
Spalte C: Abteilung
Spalte D: Name
Spalte E: Stunden

Folgendes Ähnliches Makro habe ich in alten Dateien gefunden, das müsste ich eben nun anpassen, kenne allerdings nicht die notwendigen Befehle:

Sub Stunden_kopieren()
Dim ws As Worksheet, i As Long
i = 2
'Wir starten in Zeile 2, da Zeile 1 für die Spaltenüberschriften reserviert ist
For Each ws In ThisWorkbook.Worksheets
'Hier müsste nun auf alle Workbooks im Folder verwiesen werden, mit ausnahme des target workbooks.
'Die kommende Regel gilt für JEDES Tabellenblatt in dieser Arbeitsmappe
If Not ws.Name = "Summary" Then
'Einschränkung der Regel: Ausnahme = Tabellenblatt 1, wo die Summen gebildet werden
Sheets("YTD JUNE 09").Range("B" & i).Value = ws.Range("I4").Value
'Monat
Sheets("YTD JUNE 09").Range("C" & i).Value = ws.Range("A4").Value
'Abteilung
Sheets("YTD JUNE 09").Range("D" & i).Value = ws.Range("A6").Value
'Name
Sheets("YTD JUNE 09").Range("E" & i).Value = ws.Range("S41").Value
'Summe Stunden
i = i + 1

End If
Next ws
End Sub

Ist das ausreichend erklärt? Bitte lasst mich wissen, falls ihr weiteren Erklärungsbedarf habt.

Vielen Dank!!!!!

Hallo jeanpaul23!

Target.Spalte B Monat?

Sollen die Summen pro Monat aufgeführt werden oder die Gesamt-Summe aller Monate?

Und was ist mit Target.Spalte A? Bei der Aktualisierung, werden nähmlich alle Zellen erstmal gelöscht.

Gruß Dieter

Ja die Summen werden pro Monat angezeigt.

Beispiel:

B: Juni
C: Controlling
D: Huber
E: 160

A bleibt einfach leer.

Gruß Bernd

Hallo Bernd!

Und in Monatsblatt.Spalte I4 steht der Monat?

Gruß Dieter

Hi Dieter,

das sind einfach ALLE Dateien in dem Ordner. Ich habe die Target Datei in einen anderen Ordner gepackt.
Brauchst du tatsächlich alle Namen der Arbeitsmappen. Das sind pro Abteilung und Jahr zwölf.....

Gruß
Bernd

Okay ich versuche es nochmal zu erklären

Ich habe einen Ordner: "\99 Consolidated Data\"

In diesem Ordner habe ich pro Abteilung und Monat eine Excel Arbeitsmappe.
Diese heissen "Timesheet Consolidated Finance_01 January.xls", "Timesheet Consolidated Finance_02 February.xls", "Timesheet Consolidated Marketing_01 January.xls", "Timesheet Consolidated Marketing_02 February.xls", etc....

In jeder Arbeitsmappe ist das erste Tabellenblatt eine Übersicht ("summary"), die nicht weiter interessiert. Tabellenblatt 2 bis Schluss sind die einzelnen Mitarbeiter mit ihren Arbeitszeiten.

In jedem Tabellenblatt steht in I4 der Monat, in A4 die Abteilung, in A6 der Name und in S41 die gesamte Arbeitszeit.

Nun liegt in Ordner: "\00 Target Data\" eine Arbeitsmappe namens "Target.xls". Diese Datei soll die Übersicht werden und zwar im ersten Tabellenblatt "YTD JUNE 09".

Hier soll in Spalte B der Monat, in C die Abteilung, in D der Name und in E die Arbeitszeit pro Monat eingetragen werden.

Das wäre alles

)

Danke Dir Dieter!

Gruss
Bernd

Hallo Bern!

Hatte meine Frage nochmal geändert. Steht in Monatsblatt.Spalte I4 der Monat?

Hmh, Du schreibst pro Abteilung. Also tatsächlich nur eine Abteilung in einem Ordner?

Dann währe da noch die Sortierreihenfolge zu klären. 1. Monat und 2. Name?

Gruß Dieter

Hallo Bernd!

Nicht gleich übertreiben, dass in Spalte I4 der Monat steht, war so ganz beiläufig in Deinem Makro zu sehen. In dem Fall brauch ich die Namen der Monatsblätter natürlich nicht

Und das mit pro Abteilung hattest Du vorher auch vergessen zu erwähnen. In Deinem ersten
Beitrag, war das auch anders zu verstehen.

Gruß Dieter

Sorry. Das Problem in Worte zu fassen, ist nie so ganz einfach

Wie gesagt, ich beschäftige mich damit das erste mal...

Benötigst du noch weitere Infos?

VG
Bernd

Nö, danke. Ich bin vorerst reichlich bedient

Gruß Dieter

Hallo nochmal!

Sind die Monatsnamen in Spalte I4 in deutsch oder englisch? Wichtig für die Sortierung

Gruß Dieter

Hi Dieter,

die sind in Englisch.

Gruss
Bernd

Hallo Bernd!

Hier hast Du mal was zum testen.

Noch ein paar Hinweise:

Falls das Tabellenblatt "YTD JUNE 09" doch anders heissen soll, dann ändere dies in Zeile 8 entsprechend.

Sortiert wird erst nach Monat und dann nach Name. Dafür wird die Spalte A temporär verwendet.

Die Zell-Formatierungen bleiben erhalten, da bei der Aktualisierung nur die Zellinhalte gelöscht werden.

Kopiere den Quellcode im VB-Editor in ein Modul und erstelle eine Verknüpfung mit einer Tastenkombination.

Option Explicit
Option Compare Text

Sub InitSheet()
    Dim Fso As Object, Folder As Object, File As Object, i As Integer
    Dim Wks As Worksheet, mWkb As Workbook, mWks As Worksheet

    Set Wks = ThisWorkbook.Sheets("YTD JUNE 09"):  Wks.Range("A:E").ClearContents  

    Set Fso = CreateObject("Scripting.FileSystemObject")  
    Set Folder = Fso.GetFolder(Fso.GetAbsolutePathName(ThisWorkbook.Path))

    Application.ScreenUpdating = False
    
    Wks.Range("B1:E1") = Array("Monat", "Abteilung", "Name", "Stunden"):  i = 2  
    
    For Each File In Folder.Files
        If LCase(Right(File.Name, 4)) = ".xls" And File.Name <> ThisWorkbook.Name Then  
            Set mWkb = Workbooks.Open(File.Path)
            For Each mWks In mWkb.Worksheets
                If Not mWks.Name Like "Summary" Then  
                    With mWks
                        Wks.Cells(i, 1) = GetMonth(Left(Trim(.Range("I4")), 3))    
                        Wks.Cells(i, 2) = Trim(.Range("I4"))  
                        Wks.Cells(i, 3) = Trim(.Range("A4"))  
                        Wks.Cells(i, 4) = Trim(.Range("A6"))  
                        Wks.Cells(i, 5) = .Range("S41")  
                    End With
                    i = i + 1
                End If
            Next
            mWkb.Close
        End If
    Next
    
    With Wks
        .Columns("A:E").Sort Key1:=.Range("A2"), Key2:=.Range("D2"), Header:=xlYes  
        .Columns(1).Clear
    End With
    
    Application.ScreenUpdating = True
End Sub

Private Function GetMonth(ByRef M) As Integer
    GetMonth = Switch(M = "Jan", 1, M = "Feb", 2, M = "Mar", 3, M = "Apr", 4, _  
                      M = "May", 5, M = "Jun", 6, M = "Jul", 7, M = "Aug", 8, _  
                      M = "Sep", 9, M = "Oct", 10, M = "Nov", 11, M = "Dec", 12)  
End Function

Gruß Dieter

Hallo Dieter,

wow ich bin begeistert, das schaut ja doch etwas komplizierter aus.

Ich habe deine Anweisungen befolgt, den Quelltext in ein Modul eingefügt. Wenn ich das Makro ausführen will kommt allerdings eine Fehlermeldung:

"Fehler beim Kompilieren: Benutzerdefinierter Typ nicht definiert" und in Zeile 2 ist folgender Bereich markiert: ", Folder As Folder".

Muss ich noch etwas anpassen?

Viele Grüße
Bernd

Hallo Bernd!

Das sieht nur kompliziert aus

Hhm, kann sein, dass ich einen Verweis drinnen habe, den Du nicht drinnen hast. Daher ist es am einfachsten, As Folder und As File in As Object umzuwandeln. Habe dies und noch andere Kleinigkeiten geändert. Also komplett neu kopieren.

Gruß Dieter

Hallo Dieter,

spitze das funktioniert !!! Tausend Dank dafür.

Eine Frage hätte ich allerdings noch: In meiner Auswertung hätte ich noch gerne eine Spalte (F) mit dem Titel Arbeitstage (F1). In dieser Spalte sollte folgende Excel Funktion enthalten sein:

Für Zeile 2 z.B. =WENN(ISTLEER(E2);"";E2/8). Diese Formel sollte in jeder Zelle der Spalte F sein.

Kannst du das noch irgendwie einbauen?

Manuelle wird das ja jedesmal wieder gelöscht beim Ausführen des Makros...

Vielen Dank noch einmal!

Gruß
Bernd

Hallo Bernd!

Yep, gern geschehen. Code-Zeile 8 geändert

Gruß Dieter

Hallo Dieter!

You made my day!!! Tausend dank fuer alles. Super Hilfe!

Schoenes Wochenende!

Viele Gruesse Bernd

Hallo Bernd!

Bei diesem Code, hast Du die Arbeitstage noch mit drinnen:

Option Explicit
Option Compare Text

Sub InitSheet()
    Dim Fso As Object, Folder As Folder, File As File, i As Integer
    Dim Wks As Worksheet, mWkb As Workbook, mWks As Worksheet

    Set Wks = ThisWorkbook.Sheets("YTD JUNE 09"):  Wks.Range("A:F").ClearContents  

    Set Fso = CreateObject("Scripting.FileSystemObject")  
    Set Folder = Fso.GetFolder(Fso.GetAbsolutePathName(ThisWorkbook.Path))

    Application.ScreenUpdating = False
    
    Wks.Range("B1:F1") = Array("Monat", "Abteilung", "Name", "Stunden", "Arbeitstage")  
    
    i = 2
    
    For Each File In Folder.Files
        If LCase(Right(File.Name, 4)) = ".xls" And File.Name <> ThisWorkbook.Name Then  
            Set mWkb = Workbooks.Open(File.Path)
            For Each mWks In mWkb.Worksheets
                If Not mWks.Name Like "Summary" Then  
                    With mWks
                        Wks.Cells(i, 1) = GetMonth(Left(Trim(.Range("I4")), 3))  
                        Wks.Cells(i, 2) = Trim(.Range("I4"))  
                        Wks.Cells(i, 3) = Trim(.Range("A4"))  
                        Wks.Cells(i, 4) = Trim(.Range("A6"))  
                        Wks.Cells(i, 5) = .Range("S41")  
                    End With
                    With Wks
                        If Not IsEmpty(.Cells(i, 5)) Then .Cells(i, 6) = .Cells(i, 5) / 8
                    End With
                    i = i + 1
                End If
            Next
            mWkb.Close
        End If
    Next
    
    With Wks
        .Columns("A:F").Sort Key1:=.Range("A2"), Key2:=.Range("D2"), Header:=xlYes  
        .Columns(1).Clear
    End With
    
    Application.ScreenUpdating = True
End Sub

Private Function GetMonth(ByRef M) As Integer
    GetMonth = Switch(M = "Jan", 1, M = "Feb", 2, M = "Mar", 3, M = "Apr", 4, _  
                      M = "May", 5, M = "Jun", 6, M = "Jul", 7, M = "Aug", 8, _  
                      M = "Sep", 9, M = "Oct", 10, M = "Nov", 11, M = "Dec", 12)  
End Function

Ups, habe noch die Clear-Anweisung in Zeile 8 geändert, um die Formatierung beizubehalten.

Die Formeln in Spalte F, werden bei der nächsten Aktualisierung überschrieben.

Auch Dir ein schönes WE.

Gruß Dieter

gelöst Frage Microsoft Microsoft Office

Heiß diskutiert