Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1016to1020
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

3 Schicht Dienstplan / Daten aus vorherigem Monat

3 Schicht Dienstplan / Daten aus vorherigem Monat
17.10.2008 09:23:00
Uwe
Hallo zusammen, habe mir einen funktionierenden Einsatzplan für 24 MA und 3 Schichten zusammengebastelt welcher die Monate inkl. Feiertage und Wochenenden dynamisch generiert.
Nun möchte ich wenn ich im zum Beispiel den Einsatzplan für die Mitarbeiter für Februar abrechne, das Excel sich aus dem fertig erstelltem und abgerechnetem Einsatzplan Januar die jeweiligen Urlaubstage und Überstunden mit Stand 31.01 in den Februar einliest.
Ich hatte das ganze innerhalb einer einzigen Arbeitsmappe mit 12 Blättern für die Monate bereits gelöst, leider hatte die Datei dann 59 MB. Kannste vergessen so eine Datei im Netzwerk sinvoll zu betreiben. Zur Zeit bin ich dann bei 4,5 MB und hätte so nach meiner Vorstellung wenn Excel den Vorherigen Monat mit aufmacht dann max 9 Mb im Zugriff.
Ich möchte also einen Datei öffnen Dialog haben der mir erlaubt den Januar Einsatzplan auszuwählen, am besten mit der Sicherheitsüberprüfung das der Monat der zu öffnenden Datei -1 zum bearbeitenden Monat ist. Sonst soll ein Hinweis kommen das eine falsche Datei ausgewählt worden ist.
Dann soll Excel bestimmte Zellen (immer die gleichen) auslesen pro Mitarbeiter (Urlaub/Krank/Überstunden/.../also 24 x mind. 3 Zellen = 72 Zellen und in den aktuellen Monat eintragen
Vielen Dank für euere Hilfe
Uwe
Hallo,
wie schafft man es, derartig große Dateien zu erzeugen? Das ist mir noch nie gelungen.
Gruß
Rudi
Hallo Uwe,
ohne Beispielmappe kann ich zu einer Lösung nicht beitragen.
ABER,
ich nehme an, dass Deine Mappe mit unnötigen Daten überladen ist.
Meine Erfahrung: Aus 10 MB mach 500k.
Wie?
Prüfe, ob Formatierung, Unterstriche, ...., Farben für gesamte Zeilen und/oder Spalten vergeben wurden.
Prüfe, ob wirklich nur die benutzten Zellen Werte enthalten (" " ist ungleich leer!)
LÖSCHE alle nicht benötigten Infos dieser Art und speichere unter einem neuen Namen ab.
Würde mich wundern, wenn die xlsfile nicht wesentlich kleiner würde.
Lade ein Beispiel hoch, eventuell kann ich Dir bei Deiner Problemstellung helfen.
Gruß
Firmus
Also da kann ich durchaus mal mitreden. Meine Excelmappen bekommen diese Größe locker, wenn ich so 20 Arbeitsblätter voller Sverweise und Formeln, Makros und mit Pivottabellen fertige (Geht aber auch ohne Pivot ;-)
Auch ich habe die Farben und ähnliches so wie im Tipp oben nur für die Bereich festgelegt, sogar alle leeren Zellen gelöscht bzw. entfernt, trotzdem sind die Dinger irgendwann so riesig.
Ein Problem ist dann, dass beim Speichern oder aktualisieren alles zusammenbricht und die Dateien sich komplett verabschieden - locker von 40 mb auf 6 kb. Das wars dann.
Bei den Pivottabellen hilft ein Datenmüllmakro, was mir hier vor zwei Wochen jemand netterweise geschrieben hat, (es sparte 10 MB), beim Rest half nur noch die Dateien zu teilen. Leider muss man jetzt zum Bearbeiten nun die Teildateien alle extra bearbeiten - die dreifache Arbeit.
Auf die geniale Idee warte ich noch ;-)
Gruß
Susanne
Hallo, erst einmal vielen Dank für das erste gezeigte Interesse. Dann bin ich dank Susanne etwas beruhigter das ich nicht der einzige bin mit so riesigen Excel Dateien.
Habe heute versucht die Datei kleiner zu bekommen, Erfolg lag bei 1%. Also eher nichts.
Nach dem ich dann die Datei nicht auf den Excel Server hier vom Forum hochladen konnte, auch als Zip zu groß anbei ein Link von Rapidshare
http://rapidshare.com/files/154357210/Dplan2009_Vorlage_51.zip.html
In dieser Excel Datei kann für die Stunden eingaben dann im Monatsblatt in der jeweiligen „B leiste“ des Mitarbeiters die Dienstform eingetragen werden (s=spätdienst, v=Vormittagsdienst, usw. siehe Legende in Tabelle1).
Das ganze ist ohne Passwort. Sonst ist alles bis auf die „B Leiste“ des Mitarbeiters gesperrt.

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
17.10.2008 11:04:00
fcs
Hallo Uwe,
hier mal das Grundgerüst eines Makros das die Daten aus der Vormonatsdatei holt.
Da mir nicht klar ist, aus welchen Blättern, Spalten/Zeilen die Saldodaten des Vormonats eingelesen und in welche sie eintragenwerden sollen, muss du hier anpassen.
Gruß
Franz

Sub DatenVormonatHolen()
Dim wksDienst As Worksheet, wksKopf As Worksheet, wbMonat
Dim wbVormonat As Workbook, wksDienstVor As Worksheet, wksKopfVor As Worksheet
Dim varAuswahl, bolPruefung As Boolean, strMsg As String
'Zeilenwerte für Mitarbeiter im Dienstplan, Werte ggf. anpassen
Const intMitarbeiter As Integer = 24 'Anzahl Mitarbeiter
Const intZeilenMitarbeiter As Integer = 30 'Zeilen je Mitarbeiter
Const lngZeileMitarbeiter1 As Long = 7 'Startzeile des 1. Mitarbieters
On Error GoTo Fehler
Set wksDienst = ActiveWorkbook.Worksheets("Dienstplan")
Set wksKopf = ActiveWorkbook.Worksheets("Kopf")
varAuswahl = Application.GetOpenFilename(Filefilter:="Excel(*.xls), *.xls", _
Title:="Bitte Dienstplan des Vormonats öffnen")
If varAuswahl = False Then GoTo Beenden
'Vormonatsdatei schreibgeschützt öffnen
Set wbVormonat = Application.Workbooks.Open(Filename:=varAuswahl, ReadOnly:=True)
Set wksDienstVor = wbVormonat.Worksheets("Dienstplan")
Set wksKopfVor = wbVormonat.Worksheets("Kopf")
'Vergleich Monat und Jahr im Blatt Kopf der beiden Dateien
Select Case wksKopf.Range("c1")
Case 1 'Januar
If wksKopfVor.Range("c1") = 12 _
And Year(wksKopf.Range("c2")) - Year(wksKopfVor.Range("c2")) = 1 Then
bolPruefung = True
End If
Case 2 To 12 'Februar - Dezember
If wksKopf.Range("c1") - wksKopfVor.Range("c1") = 1 _
And Year(wksKopf.Range("c2")) = Year(wksKopfVor.Range("c2")) Then
bolPruefung = True
End If
Case Else
MsgBox "Unzulässige Eingabe für Monat oder Datum im Blatt Kopf"
GoTo Beenden
End Select
If bolPruefung = True Then
'Daten aus Vormonatsblatt einlesen _
Spaltennummern sowie Werte für Offset anpassen!!!!
With wksDienst 'Blatt in das eingetragen werden soll
.Unprotect
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lngSpalte = 34 'Spalte AH = Zielspalte für Saldodaten aus Vormonat im Dienstplan
lngSpalteVor = 35 'Spalte AI = Quellspalte mit Saldodaten aus Vormonat
'Zeile 7 ist 1. Zeile des 1. Mitarbeiters
For lngZeile = lngZeileMitarbeiter1 To lngZeileMitarbeiter1 + _
(intMitarbeiter - 1) * intZeilenMitarbeiter Step intZeilenMitarbeiter
'Wert 1 übernehmen
.Cells(lngZeile, lngSpalte).Offset(0, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(0, 0).Value
'Wert 2 übernehmen
.Cells(lngZeile, lngSpalte).Offset(1, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(1, 0).Value
'Wert 3 übernehmen
.Cells(lngZeile, lngSpalte).Offset(2, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(2, 0).Value
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
.Protect
End With
Else
MsgBox "Die geöffnete Datei enthält nicht Daten des Vormonats"
End If
Fehler:
If Err.Number  0 Then
strMsg = "Fehler-Nr. " & Err.Number & vbLf & Err.Description
If Not wbVormonat Is Nothing And wksDienstVor Is Nothing Then
strMsg = strMsg & vbLf & "Blatt ""Dienstplan"" in geöffneter Datei nicht vorhanden"
ElseIf Not wbVormonat Is Nothing And wksKopfVor Is Nothing Then
strMsg = strMsg & vbLf & "Blatt ""Kopf"" in geöffneter Datei nicht vorhanden"
End If
MsgBox strMsg
End If
Beenden:
If Not wbVormonat Is Nothing Then
If MsgBox("Soll geöffnete Datei mit Daten des Vormonats wieder geschlossen werden?", _
vbYesNo) = vbYes Then wbVormonat.Close savechanges:=False
End If
End Sub


Anzeige
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
18.10.2008 14:18:10
Uwe
Hallo FSC (Franz)
Vielen Dank, nach Anpassung funktioniert es wunderbar. Aber (ja ja nie zufrieden)
Wenn ich durch das makro richtig durchblicke holt es sich die Daten aus der Seite Dienstplan, weiterhin geht das makro davon aus das zwischen den einzelnen Mitarbeitern immer die gleiche anzahl von Zeilen ist. Also liest er z.B. Zeile 7 Spalte AG aus dann Zeile 37,67 usw.
Diese Technik würde auch gut funktionieren, wenn ich nicht so diverse Fehler begangen hätte und der besagte Abstand nicht immer der gleiche ist. Bei der Stundenübernahme aus dem Vormonat habe ich das Makro so angepasst das er nun die Daten aus dem Tabellenblatt Kopf nimmt. Denn da ist immer der gleiche Zeilenabstand.
Wie muss ich nun das Makro anpassen damit ich direkt die Zellen angeben (z.B. ag43,b56,...,) kann die er auslesen soll. Und wohin er sie schreiben soll. Ist das überhaupt möglich?
Oder sollte ich das Kopf Blatt so verändern das ich auch von dort die benötigten Werte
(Urlaub Rest u. Urlaub verbraucht) wie bei den Überstunden auslesen kann.
Vielen Dank bis hierhin ersteinmal. Sogar die Abfrage nach dem vorherigen Monat funktionierte Auf Anhieb, super sache.
Anzeige
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
20.10.2008 08:56:51
fcs
Hallo Uwe,
du hast ja in einem Blatt die Namen aller Mitarbeiter.
In einer For-Next-Schleife könnte man diese Mitarbeiternamen in der Spalte A im Blatt Dienstplann suchen und so die Zeile ermitteln, aus der die relevanten Daten ausgelesen werden sollen.
Diese Suche muss man ggf. sowhl im Vormonats-Blatt machen als auch im Dienstplan in dem die dtaen eingetragen werden sollen.
Gruß
Franz
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
20.10.2008 16:42:35
Uwe

Kaum Excel/VBA-Kenntnisse


Also hört sich gut an aber meine Kenntnisse schleifen betreffen sehen ungefähr so aus
a=1
For a=a+1
next a
Ich denke da ist wohl noch etwas Ergänzung nötig.
Und ihm einfach direkt die besagten Zellen in einer Formel mitteilen, ohne Automatik? das geht nicht oder sehe ich das falsch
lg / Uwe

Anzeige
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
20.10.2008 17:59:00
fcs
Hallo Uwe,
unregelmäßig aufgebaute Tabellen per Makro abzuarbeiten ist immer ziemlich aufwendig zu programmieren.
Die manuelle Lösung heißt dann die Zeilennummern fest in den Code eintragen, was dann bei ändeungen oder Ergänzungen an den Tabellen immer auch Code-Änderungen erfordert.
Alternativ könnte man die Informationen zu den Zeilen/Zellen im Blatt "Dienstplan" im Blatt "Kopf" für jeden Mitarbeiter hinterlegen und vom Makro auslesen lassen.
Nachfolgend ein angepasster Code, bei dem die Zeileninformation je Mitarbeiter im Code hinterlegt ist (die schlechteste Lösung von allen).
gruß
Franz

Sub DatenVormonatHolen()
Dim wksDienst As Worksheet, wksKopf As Worksheet, wbMonat
Dim wbVormonat As Workbook, wksDienstVor As Worksheet, wksKopfVor As Worksheet
Dim varAuswahl, bolPruefung As Boolean, strMsg As String
Dim intMit As Integer
'Zeilenwerte für Mitarbeiter im Dienstplan, Werte ggf. anpassen
Const intMitarbeiter As Integer = 24 'Anzahl Mitarbeiter
On Error GoTo Fehler
Set wksDienst = ActiveWorkbook.Worksheets("Dienstplan")
Set wksKopf = ActiveWorkbook.Worksheets("Kopf")
varAuswahl = Application.GetOpenFilename(Filefilter:="Excel(*.xls), *.xls", _
Title:="Bitte Dienstplan des Vormonats öffnen")
If varAuswahl = False Then GoTo Beenden
'Vormonatsdatei schreibgeschützt öffnen
Set wbVormonat = Application.Workbooks.Open(Filename:=varAuswahl, ReadOnly:=True)
Set wksDienstVor = wbVormonat.Worksheets("Dienstplan")
Set wksKopfVor = wbVormonat.Worksheets("Kopf")
'Vergleich Monat und Jahr im Blatt Kopf der beiden Dateien
Select Case wksKopf.Range("c1")
Case 1 'Januar
If wksKopfVor.Range("c1") = 12 _
And Year(wksKopf.Range("c2")) - Year(wksKopfVor.Range("c2")) = 1 Then
bolPruefung = True
End If
Case 2 To 12 'Februar - Dezember
If wksKopf.Range("c1") - wksKopfVor.Range("c1") = 1 _
And Year(wksKopf.Range("c2")) = Year(wksKopfVor.Range("c2")) Then
bolPruefung = True
End If
Case Else
MsgBox "Unzulässige Eingabe für Monat oder Datum im Blatt Kopf"
GoTo Beenden
End Select
If bolPruefung = True Then
'Daten aus Vormonatsblatt einlesen _
Spaltennummern sowie Werte für Offset anpassen!!!!
With wksDienst 'Blatt in das eingetragen werden soll
.Unprotect
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lngSpalte = 34 'Spalte AH = Zielspalte für Saldodaten aus Vormonat im Dienstplan
lngSpalteVor = 35 'Spalte AI = Quellspalte mit Saldodaten aus Vormonat
For lngMit = 1 To intMitarbeiter
'in den nachfolgenden Zeilen die Bezugszeilen für die Werte eingeben _
falls auch die Spalten und Offsetwerte variieren, dann müssen diese ebenfalls _
in den Case-Zeilen als Variablen festgelegt werden und unten in den Anweisungen _
für das Eintragen der Werte die Zahlen durch die entsprechenden Variablen ersetzt _
werden.
Select Case lngMit
Case 1: lngZeile = 7
Case 2: lngZeile = 37
Case 3: lngZeile = 67
Case 4: lngZeile = 97
Case 5: lngZeile = 127
Case 6: lngZeile = 157
Case 7: lngZeile = 187
Case 8: lngZeile = 217
Case 9: lngZeile = 247
Case 10: lngZeile = 277
Case 11: lngZeile = 307
Case 12: lngZeile = 337
Case 13: lngZeile = 367
Case 14: lngZeile = 397
Case 15: lngZeile = 327
Case 16: lngZeile = 357
Case 17: lngZeile = 387
Case 18: lngZeile = 417
Case 19: lngZeile = 447
Case 20: lngZeile = 477
Case 21: lngZeile = 507
Case 22: lngZeile = 537
Case 23: lngZeile = 567
Case 24: lngZeile = 597
Case Else
MsgBox "Für Mitarbeiter nr. " & intMit _
& " wurde noch keine Case-Zeile im Code angelegt"
End Select
'Wert 1 übernehmen
.Cells(lngZeile, lngSpalte).Offset(0, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(0, 0).Value
'Wert 2 übernehmen
.Cells(lngZeile, lngSpalte).Offset(1, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(1, 0).Value
'Wert 3 übernehmen
.Cells(lngZeile, lngSpalte).Offset(2, 0).Value = _
wksDienstVor.Cells(lngZeile, lngSpalteVor).Offset(2, 0).Value
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
.Protect
End With
Else
MsgBox "Die geöffnete Datei enthält nicht Daten des Vormonats"
End If
Fehler:
If Err.Number  0 Then
strMsg = "Fehler-Nr. " & Err.Number & vbLf & Err.Description
If Not wbVormonat Is Nothing And wksDienstVor Is Nothing Then
strMsg = strMsg & vbLf & "Blatt ""Dienstplan"" in geöffneter Datei nicht vorhanden"
ElseIf Not wbVormonat Is Nothing And wksKopfVor Is Nothing Then
strMsg = strMsg & vbLf & "Blatt ""Kopf"" in geöffneter Datei nicht vorhanden"
End If
MsgBox strMsg
End If
Beenden:
If Not wbVormonat Is Nothing Then
If MsgBox("Soll geöffnete Datei mit Daten des Vormonats wieder geschlossen werden?", _
vbYesNo) = vbYes Then wbVormonat.Close savechanges:=False
End If
End Sub


Anzeige
AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat
27.10.2008 22:13:00
Uwe
Also genau so sollte das sein, Vielen Dank. Kompliment das du dich in eine wie ich finde komplexe Excel Mappe so gut reindenken konntest.
Einen kleinen schönheitsfehler gibt es noch, er liest nur den ersten Wert aus, die anderen beiden offset anweisungen bearbeitet er zwar hat aber hat kein Ergebnis was er in die Zellen einträgt. Zur Zeit habe ich das makro 3 mal kopiert und die Zellinformation an der Stelle für den ersten Wert in der 2 u 3 kopie abgeändert und starte das ganze mit einem call makro. Da nervt dann beim 2 und 3 mal diese (ehrlich super gelöste) Dateiimport öffnen Lösung.
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige