jeanpaul23
Jul 15, 2009, updated at 08:08:12 (UTC)
view6153
view23
0
Goto Top

Wert fester Zellen verschiedener Dateien auslesen

GermansolvedQuestionMicrosoft OfficeMicrosoft

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!
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 120493

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

Printed on: April 25, 2024 at 08:04 o'clock

23 Comments
Latest comment
Goto Top
Member: Maburaho21
Maburaho21 Jul 15, 2009 at 08:28:26 (UTC)
Goto Top
hast du dabei an ein Makro gedacht?
Member: jeanpaul23
jeanpaul23 Jul 15, 2009 at 08:31:23 (UTC)
Goto Top
Hallo Maburaho21,

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

Danke!!
Member: Maburaho21
Maburaho21 Jul 15, 2009 at 11:55:53 (UTC)
Goto Top
hast du schon mal ein excel-makro programmiert?
Mitglied: 76109
76109 Jul 15, 2009 at 12:55:03 (UTC)
Goto Top
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
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 10:15:31 (UTC)
Goto Top
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!!!!!
Mitglied: 76109
76109 Jul 16, 2009 at 11:22:31 (UTC)
Goto Top
Hallo jeanpaul23!

Target.Spalte B Monat?

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

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

Gruß Dieter
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 11:40:00 (UTC)
Goto Top
Ja die Summen werden pro Monat angezeigt.

Beispiel:

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

A bleibt einfach leer.

Gruß Bernd
Mitglied: 76109
76109 Jul 16, 2009 at 12:03:27 (UTC)
Goto Top
Hallo Bernd!

Und in Monatsblatt.Spalte I4 steht der Monat?

Gruß Dieter
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 12:20:34 (UTC)
Goto Top
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
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 12:30:22 (UTC)
Goto Top
Okay ich versuche es nochmal zu erklären face-wink

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 face-wink)

Danke Dir Dieter!

Gruss
Bernd
Mitglied: 76109
76109 Jul 16, 2009 at 12:35:53 (UTC)
Goto Top
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
Mitglied: 76109
76109 Jul 16, 2009 at 12:45:51 (UTC)
Goto Top
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 nichtface-smile

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
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 12:49:06 (UTC)
Goto Top
Sorry. Das Problem in Worte zu fassen, ist nie so ganz einfach face-wink Wie gesagt, ich beschäftige mich damit das erste mal...

Benötigst du noch weitere Infos?

VG
Bernd
Mitglied: 76109
76109 Jul 16, 2009 at 12:56:01 (UTC)
Goto Top
Nö, danke. Ich bin vorerst reichlich bedientface-smile

Gruß Dieter
Mitglied: 76109
76109 Jul 16, 2009 at 13:37:52 (UTC)
Goto Top
Hallo nochmal!

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

Gruß Dieter
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 13:39:49 (UTC)
Goto Top
Hi Dieter,

die sind in Englisch.

Gruss
Bernd
Mitglied: 76109
76109 Jul 16, 2009 at 16:01:57 (UTC)
Goto Top
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
Member: jeanpaul23
jeanpaul23 Jul 16, 2009 at 20:35:33 (UTC)
Goto Top
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
Mitglied: 76109
76109 Jul 16, 2009 at 22:57:39 (UTC)
Goto Top
Hallo Bernd!

Das sieht nur kompliziert ausface-smile

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
Member: jeanpaul23
jeanpaul23 Jul 17, 2009 at 08:00:09 (UTC)
Goto Top
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
Mitglied: 76109
76109 Jul 17, 2009 at 11:19:14 (UTC)
Goto Top
Hallo Bernd!

Yep, gern geschehen. Code-Zeile 8 geändertface-smile

Gruß Dieter
Member: jeanpaul23
jeanpaul23 Jul 18, 2009 at 20:16:56 (UTC)
Goto Top
Hallo Dieter!

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

Schoenes Wochenende!

Viele Gruesse Bernd
Mitglied: 76109
76109 Jul 19, 2009 at 07:21:54 (UTC)
Goto Top
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
GermansolvedQuestionMicrosoft OfficeMicrosoft
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments