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
GELÖST

Wert fester Zellen verschiedener Dateien auslesen

Frage Microsoft Microsoft Office

Mitglied: jeanpaul23

jeanpaul23 (Level 1) - Jetzt verbinden

15.07.2009, aktualisiert 10:08 Uhr, 5530 Aufrufe, 23 Kommentare

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!
Mitglied: Maburaho21
15.07.2009 um 10:28 Uhr
hast du dabei an ein Makro gedacht?
Bitte warten ..
Mitglied: jeanpaul23
15.07.2009 um 10:31 Uhr
Hallo Maburaho21,

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

Danke!!
Bitte warten ..
Mitglied: Maburaho21
15.07.2009 um 13:55 Uhr
hast du schon mal ein excel-makro programmiert?
Bitte warten ..
Mitglied: 76109
15.07.2009 um 14:55 Uhr
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
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 12:15 Uhr
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!!!!!
Bitte warten ..
Mitglied: 76109
16.07.2009 um 13:22 Uhr
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
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 13:40 Uhr
Ja die Summen werden pro Monat angezeigt.

Beispiel:

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

A bleibt einfach leer.

Gruß Bernd
Bitte warten ..
Mitglied: 76109
16.07.2009 um 14:03 Uhr
Hallo Bernd!

Und in Monatsblatt.Spalte I4 steht der Monat?

Gruß Dieter
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 14:20 Uhr
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
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 14:30 Uhr
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
Bitte warten ..
Mitglied: 76109
16.07.2009 um 14:35 Uhr
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
Bitte warten ..
Mitglied: 76109
16.07.2009 um 14:45 Uhr
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
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 14:49 Uhr
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
Bitte warten ..
Mitglied: 76109
16.07.2009 um 14:56 Uhr
Nö, danke. Ich bin vorerst reichlich bedient

Gruß Dieter
Bitte warten ..
Mitglied: 76109
16.07.2009 um 15:37 Uhr
Hallo nochmal!

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

Gruß Dieter
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 15:39 Uhr
Hi Dieter,

die sind in Englisch.

Gruss
Bernd
Bitte warten ..
Mitglied: 76109
16.07.2009 um 18:01 Uhr
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.
01.
Option Explicit 
02.
Option Compare Text 
03.
 
04.
Sub InitSheet() 
05.
    Dim Fso As Object, Folder As Object, File As Object, i As Integer 
06.
    Dim Wks As Worksheet, mWkb As Workbook, mWks As Worksheet 
07.
 
08.
    Set Wks = ThisWorkbook.Sheets("YTD JUNE 09"):  Wks.Range("A:E").ClearContents 
09.
 
10.
    Set Fso = CreateObject("Scripting.FileSystemObject") 
11.
    Set Folder = Fso.GetFolder(Fso.GetAbsolutePathName(ThisWorkbook.Path)) 
12.
 
13.
    Application.ScreenUpdating = False 
14.
     
15.
    Wks.Range("B1:E1") = Array("Monat", "Abteilung", "Name", "Stunden"):  i = 2 
16.
     
17.
    For Each File In Folder.Files 
18.
        If LCase(Right(File.Name, 4)) = ".xls" And File.Name <> ThisWorkbook.Name Then 
19.
            Set mWkb = Workbooks.Open(File.Path) 
20.
            For Each mWks In mWkb.Worksheets 
21.
                If Not mWks.Name Like "Summary" Then 
22.
                    With mWks 
23.
                        Wks.Cells(i, 1) = GetMonth(Left(Trim(.Range("I4")), 3))   
24.
                        Wks.Cells(i, 2) = Trim(.Range("I4")) 
25.
                        Wks.Cells(i, 3) = Trim(.Range("A4")) 
26.
                        Wks.Cells(i, 4) = Trim(.Range("A6")) 
27.
                        Wks.Cells(i, 5) = .Range("S41") 
28.
                    End With 
29.
                    i = i + 1 
30.
                End If 
31.
            Next 
32.
            mWkb.Close 
33.
        End If 
34.
    Next 
35.
     
36.
    With Wks 
37.
        .Columns("A:E").Sort Key1:=.Range("A2"), Key2:=.Range("D2"), Header:=xlYes 
38.
        .Columns(1).Clear 
39.
    End With 
40.
     
41.
    Application.ScreenUpdating = True 
42.
End Sub 
43.
 
44.
Private Function GetMonth(ByRef M) As Integer 
45.
    GetMonth = Switch(M = "Jan", 1, M = "Feb", 2, M = "Mar", 3, M = "Apr", 4, _ 
46.
                      M = "May", 5, M = "Jun", 6, M = "Jul", 7, M = "Aug", 8, _ 
47.
                      M = "Sep", 9, M = "Oct", 10, M = "Nov", 11, M = "Dec", 12) 
48.
End Function
Gruß Dieter
Bitte warten ..
Mitglied: jeanpaul23
16.07.2009 um 22:35 Uhr
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
Bitte warten ..
Mitglied: 76109
17.07.2009 um 00:57 Uhr
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
Bitte warten ..
Mitglied: jeanpaul23
17.07.2009 um 10:00 Uhr
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
Bitte warten ..
Mitglied: 76109
17.07.2009 um 13:19 Uhr
Hallo Bernd!

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

Gruß Dieter
Bitte warten ..
Mitglied: jeanpaul23
18.07.2009 um 22:16 Uhr
Hallo Dieter!

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

Schoenes Wochenende!

Viele Gruesse Bernd
Bitte warten ..
Mitglied: 76109
19.07.2009 um 09:21 Uhr
Hallo Bernd!

Bei diesem Code, hast Du die Arbeitstage noch mit drinnen:
01.
Option Explicit 
02.
Option Compare Text 
03.
 
04.
Sub InitSheet() 
05.
    Dim Fso As Object, Folder As Folder, File As File, i As Integer 
06.
    Dim Wks As Worksheet, mWkb As Workbook, mWks As Worksheet 
07.
 
08.
    Set Wks = ThisWorkbook.Sheets("YTD JUNE 09"):  Wks.Range("A:F").ClearContents 
09.
 
10.
    Set Fso = CreateObject("Scripting.FileSystemObject") 
11.
    Set Folder = Fso.GetFolder(Fso.GetAbsolutePathName(ThisWorkbook.Path)) 
12.
 
13.
    Application.ScreenUpdating = False 
14.
     
15.
    Wks.Range("B1:F1") = Array("Monat", "Abteilung", "Name", "Stunden", "Arbeitstage") 
16.
     
17.
    i = 2 
18.
     
19.
    For Each File In Folder.Files 
20.
        If LCase(Right(File.Name, 4)) = ".xls" And File.Name <> ThisWorkbook.Name Then 
21.
            Set mWkb = Workbooks.Open(File.Path) 
22.
            For Each mWks In mWkb.Worksheets 
23.
                If Not mWks.Name Like "Summary" Then 
24.
                    With mWks 
25.
                        Wks.Cells(i, 1) = GetMonth(Left(Trim(.Range("I4")), 3)) 
26.
                        Wks.Cells(i, 2) = Trim(.Range("I4")) 
27.
                        Wks.Cells(i, 3) = Trim(.Range("A4")) 
28.
                        Wks.Cells(i, 4) = Trim(.Range("A6")) 
29.
                        Wks.Cells(i, 5) = .Range("S41") 
30.
                    End With 
31.
                    With Wks 
32.
                        If Not IsEmpty(.Cells(i, 5)) Then .Cells(i, 6) = .Cells(i, 5) / 8 
33.
                    End With 
34.
                    i = i + 1 
35.
                End If 
36.
            Next 
37.
            mWkb.Close 
38.
        End If 
39.
    Next 
40.
     
41.
    With Wks 
42.
        .Columns("A:F").Sort Key1:=.Range("A2"), Key2:=.Range("D2"), Header:=xlYes 
43.
        .Columns(1).Clear 
44.
    End With 
45.
     
46.
    Application.ScreenUpdating = True 
47.
End Sub 
48.
 
49.
Private Function GetMonth(ByRef M) As Integer 
50.
    GetMonth = Switch(M = "Jan", 1, M = "Feb", 2, M = "Mar", 3, M = "Apr", 4, _ 
51.
                      M = "May", 5, M = "Jun", 6, M = "Jul", 7, M = "Aug", 8, _ 
52.
                      M = "Sep", 9, M = "Oct", 10, M = "Nov", 11, M = "Dec", 12) 
53.
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
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...

Windows Tools
gelöst Aussendienst Datensynchronisierung (12)

Frage von lighningcrow zum Thema Windows Tools ...

Windows Server
RODC über VPN - Verbindung weg (10)

Frage von stefan2k1 zum Thema Windows Server ...