Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

3 Schicht Dienstplan / Daten aus vorherigem Monat | Herbers Excel-Forum


Betrifft: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: Uwe
Geschrieben am: 17.10.2008 09:23:46

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



Betrifft: AW: Daten aus einer anderen Mappe auslesen
von: Rudi Maintaire
Geschrieben am: 14.10.2008 22:31:48

Hallo,
wie schafft man es, derartig große Dateien zu erzeugen? Das ist mir noch nie gelungen.

Gruß
Rudi



Betrifft: AW: Daten aus einer anderen Mappe auslesen
von: firmus
Geschrieben am: 15.10.2008 08:08:53

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



Betrifft: AW: Daten aus einer anderen Mappe auslesen
von: susanne
Geschrieben am: 15.10.2008 18:45:34

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



Betrifft: AW: Daten aus einer anderen Mappe auslesen
von: Uwe
Geschrieben am: 15.10.2008 22:04:22

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.


  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: fcs
Geschrieben am: 17.10.2008 11:04:30

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




  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: Uwe
Geschrieben am: 18.10.2008 14:18:10

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.


  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: fcs
Geschrieben am: 20.10.2008 08:56:51

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


  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: Uwe
Geschrieben am: 20.10.2008 16:42:35

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


  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: fcs
Geschrieben am: 20.10.2008 17:59:17

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




  

Betrifft: AW: 3 Schicht Dienstplan / Daten aus vorherigem Monat von: Uwe
Geschrieben am: 27.10.2008 22:13:55

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.


Beiträge aus den Excel-Beispielen zum Thema "3 Schicht Dienstplan / Daten aus vorherigem Monat "