Microsoft Excel

Herbers Excel/VBA-Archiv

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

Formatierung einer aus Access importierten Tabelle


Betrifft: Formatierung einer aus Access importierten Tabelle von: Jonas Albrecht
Geschrieben am: 12.04.2018 10:02:08

Hallo, ich habe glaube ich eine etwas spezielle Fragestellung, aber vielleicht kann mir ja jemand weiterhelfen.

Folgendes: ich möchte in Access eine Art Anwesenheitsplanung erstellen. Darin soll den Mitarbeitern ihre Schicht in der Vorplanung zunächst wöchentlich, später auch täglich, zugeteilt werden. Sieht in etwa so aus: Spalte 1: Personalnummer, Spalte 2: Schichtart, Spalte 3: Datum von, Spalte 4: Datum bis. Diese Tabelle soll in Excel eingefügt werden. Mitteles VBA möchte ich nun erzielen, dass die beiden Spalten "Datum von" und "Datum bis" in Spalten für jeden einzelnen Tag aufgeschlüsselt werden und dort die Schichtart aus der zweiten Spalte übertragen wird. Über die personalnummer des mitarbeiters soll dann die Schichtplanung für das ganze Jahr in eine Zeile geschrieben werden. Ändert sich in Spalte 1 der Access Tabelle die Personalnummer soll eine neue Zeile geschrieben werden.

Zur besseren Veranschaulichung lade ich Tabelle mal hoch.

https://www.herber.de/bbs/user/121018.xlsx

Ich bin mir nicht sicher, ob das alles überhaupt so fuktioniert wie ich mir das vorstelle, aber vielleicht kann mir ja einer weiterhelfen. Danke

  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Robert Werner
Geschrieben am: 12.04.2018 15:59:35

Hallo Jonas,

schau Dir mal beiliegende Datei an. Ich gehe mal davon aus, dass die Access-Daten in Excel mittels "Externe Daten" importiert werden und in dem Tabellenblatt AccessDaten stehen. Sollte das Tabellenblatt anders heißen, müsste das an der entsprechenden Stelle im Makro angepasst werden.

Auf dem Blatt Schichtplan kann man dann in der Zelle B1 das Jahr, für das der Plan erstellt werden soll auswählen. Mit einem Klick auf den Button "Schichtplan erstellen" wird dann der Schichtplan ab der Zelle A3 erstellt.
https://www.herber.de/bbs/user/121027.xlsm

Schau Dir die Datei mal an und probiere es mit Deinen, wahrscheinlich etwas umfangreicheren Daten, vielleicht funktioniert es ja.

Gruß
Robert


  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: fcs
Geschrieben am: 12.04.2018 16:19:04

Hallo Jonas,

hier zwei Makros,
eines erstellt ein Tabellenblatt mit einem Kalender mit den fortlaufenden Tagen für ein Jahr,

Das 2. Überträgt Daten aus deiner Liste in den Kalender.

Gruß
Franz

Sub MakeKalender()
  'Erstellt in einem neuen Tabellenblatt einen Kalender mit den Tagen des Jahres in Zeile 2
  Dim Jahr As Long
  Dim wks As Worksheet
  Const SpaDatum1 As Long = 2 'Spale B  -  Spalte mit dem 1. Datum im Kalender - ggf. anpasen
  Const ZeiDatum As Long = 2 'Zeile mit den Datumswerten im Kalender - ggf. anpasen
EingabeJahr:
  Jahr = Application.InputBox("Welches Jahr?", "Kalender erstellen", Year(Date), 1)
  Select Case Jahr
    Case 0
      'abgebrochen
    Case 1900 To 9999
      Set wks = ActiveWorkbook.Worksheets.Add(After:=ActiveSheet)
      With wks
        .Cells(ZeiDatum - 1, SpaDatum1 - 1) = "Jahr"
        .Cells(ZeiDatum - 1, SpaDatum1) = Jahr
        .Cells(ZeiDatum, SpaDatum1 - 1) = "Pers.-Nr"
        .Cells(ZeiDatum, SpaDatum1) = DateSerial(Jahr, 1, 1) '1. Januar des Jahres
        .Cells(ZeiDatum, SpaDatum1).NumberFormat = "DD.MM.YY"
        With .Range(.Cells(ZeiDatum, SpaDatum1 + 1), _
          .Cells(ZeiDatum, DateSerial(Jahr, 12, 31) - .Cells(ZeiDatum, SpaDatum1) + SpaDatum1))
          .NumberFormat = "DD.MM.YY"
          .FormulaR1C1 = "=RC[-1]+1"
          .Calculate
          .Value = .Value
        End With
        
        .UsedRange.EntireColumn.AutoFit
        
        ActiveSheet.Cells(ZeiDatum + 1, SpaDatum1 + 1).Select
        ActiveWindow.FreezePanes = True
        .Name = "Kalender"
      End With
    Case Else
      MsgBox "unzulässiger Wert für Jahr", vbOKOnly, "Kalender erstellen"
      GoTo EingabeJahr
  End Select
End Sub

Sub AusfuellenKalender()
  Dim wksData As Worksheet, Zei_D As Long, objList As ListObject, sListName As String
  Dim wksKal As Worksheet, Zei_K As Long, Zei_L As Long
  Dim varPersNr, varSchicht, datStart As Date, datEnde As Date
  Dim datStartKal As Date, datEndeKal As Date
  Dim Spa_K1 As Long, Spa_K2 As Long, Spa_KL As Long
  
  Const SpaDatum1 As Long = 2 'Spale B  -  Spalte mit dem 1. Datum im Kalender - ggf. anpasen
  Const ZeiDatum As Long = 2 'Zeile mit den Datumswerten im Kalender - ggf. anpasen
  
  Set wksKal = ActiveWorkbook.Worksheets("Kalender") 'Blattname ggf. anpassen
  Set wksData = ActiveWorkbook.Worksheets("Tabelle2") 'Blattname ggf. anpassen
  
  'Alt-Daten im Kalenderblatt löschen
  With wksKal
    'letzte Zeile mit Daten in Spalte A
    Zei_L = .Cells(.Rows.Count, SpaDatum1 - 1).End(xlUp).Row
    If Zei_L > ZeiDatum Then
      'vorhandenen Daten löschen
      .Range(.Rows(ZeiDatum + 1), .Rows(Zei_L)).ClearContents
    End If
    'letzte Spalte mit Datumswert
    Spa_KL = .Cells(ZeiDatum, .Columns.Count).End(xlToLeft).Column 'letzte Spalte in Zeile 2
    '1. Datum im Kalender
    datStartKal = .Cells(ZeiDatum, SpaDatum1).Value
    'letztes Datum im Kalender
    datEndeKal = .Cells(ZeiDatum, Spa_KL).Value
    Zei_K = ZeiDatum 'Zeilenzähler für Personal-Nummern im Kalender
  End With
 '
 'Importierte Personalliste mit Schichtangaben abarbeiten
  With wksData
    Set objList = .ListObjects(1)
    sListName = objList.Name
    'Daten in Tabelle (ListObject) sortieren nach Personalnummer und Startdatum
    objList.Sort.SortFields.Clear
    objList.Sort.SortFields.Add Key:=.Range(sListName & "[Personalnummer]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    objList.Sort.SortFields.Add Key:=Range(sListName & "[Startdatum]"), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With objList.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  End With
    
  'Daten im Kalenderblatt eintragen
  With objList.DataBodyRange
    For Zei_D = 1 To .Rows.Count
      Spa_K1 = 0: Spa_K2 = 0
      'prüfen, ob neue Personal-Nummer
      If varPersNr <> .Cells(Zei_D, 2).Value Then
        varPersNr = .Cells(Zei_D, 2)
        Zei_K = Zei_K + 1
'        wksKal.Cells(Zei_K, 1) = varPersNr
        wksKal.Cells(Zei_K, 1) = "'" & varPersNr 'Personal-Nr. als Text eintragen (mit fü _
hrenden Nullen)
      End If
      'Daten in Variablen einlesen
      varSchicht = .Cells(Zei_D, 3).Value
      datStart = .Cells(Zei_D, 4).Value
      datEnde = .Cells(Zei_D, 5).Value
      'prüfen, ob Datums-Werte im Bereich des Kalenders liegen
      If datStart < datStartKal Then
        Spa_K1 = SpaDatum1
      ElseIf datStart <= datEndeKal Then
        Spa_K1 = SpaDatum1 + (datStart - datStartKal)
      End If
      If datEnde > datEndeKal Then
        Spa_K2 = Spa_KL
      ElseIf datEnde >= datStartKal Then
        Spa_K2 = SpaDatum1 + (datEnde - datStartKal)
      End If
      If Spa_K2 = 0 Or Spa_K1 = 0 Then
          'außerhalb Datumsbereich
      Else
        'Schichtwert eintragen für Datumsbereich
        With wksKal
          .Range(.Cells(Zei_K, Spa_K1), .Cells(Zei_K, Spa_K2)).Value = varSchicht
        End With
      End If
    Next
  End With
End Sub



  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Jonas Albrecht
Geschrieben am: 16.04.2018 14:00:04

Hallo Robert die Datei sieht sehr viel versprechend aus! Leider verstehe ich den Code noch nicht so richtig. Zusätzlich zur Personalnummer, der Schicht und den Datumsangaben, kommen noch ein paar Daten aus der Access hinzu. Sprich Name, Vorname usw. Wie kann ich das Array um diese weiteren Spalten erweitern und mit den entsprechenden Daten füllen?


  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Robert Werner
Geschrieben am: 16.04.2018 20:45:25

Hallo Jonas,

die Tabelle mit den Access-Daten wird in das Array arrQuelle eingelesen. Das Makro ist schneller, wenn die Datenverarbeitung aus einem Array heraus geschieht statt jedes Mal auf eine Tabellenzelle zugreifen zu müssen. Bei wenigen Daten ist dies noch unerheblich, bei umfangreicheren Daten merkt man die unterschiedliche Geschwindigkeit doch ganz schön.

Das Array arrQuelle ist nach der Zuweisung also ein zweidimensionales Array mit den Daten aus Deiner Tabelle. Die erste Dimension dieses Arrays stellt die Zeilen der Tabelle, die zweite Dimension die Spalten dar:
ArrQuelle(1,1) bis arrQuelle(1,6) enthält die Überschriften, also AnwesenheitsID bis Kalenderwoche. Wenn noch 5 zusätzliche Spalten mit Vorname, Name usw. enthalten sind, geht das Arry halt bis arrQuelle(1,11).
Der erste Datensatz sieht dann wie folgt aus:
arrQuelle(2,1) enthält die AnwesenheitsID: 2
arrQuelle(2,2) enthält die Personal-Nr: 0001
arrQuelle(2,3) enthält die Anwesenheitsart2: S
arrQuelle(2,4) enthält das Startdatum: 30.12.2017
arrQuelle(2,5) enthält das Enddatum: 05.01.2018
arrQuelle(2,6) enthält die Kalenderwoche: 02.01.2018

Der Zweite Datensatz geht dann von arrQuelle(3,1) bis arrQuelle(3,6) usw. Wenn noch mehr Spalten in der Tabelle enthalten sind entsprechend weiter.

In das zweite Array arrSchicht werden die Daten nach Berechnung, wann welche Schicht zu erledigen ist, befüllt. Wenn ein Array im Laufe des Makrolaufes mit berechneten Daten befüllt werden soll, muss vorher definiert werden, wie viele Elemente in den einzelnen Dimensionen enthalten sind. Dies geschieht hier mit der Anweisung ReDim Preserve arrSchicht(366, 0). Da standardmässig diese Arrays nullbasiert sind, können nach o. a. Anweisung in diesem Array 367 Daten in der ersten Dimension und 1 Datum in der zweiten Dimension gespeichert werden. Nachträglich lässt sich nur die Größe der zweiten Dimension ändern. Da man in Deinem Fall zwar vorher die Anzahl der Spalten kennt, nicht aber die Anzahl der notwendigen Zeilen, musste ich deshalb in dem Array die Spalten in der ersten Dimension und die Zeilen (also die einzelnen Datensätze) in der zweiten Dimension speichern. Sobald eine neue PersonalNr. kommt, wird die Größe der zweiten Dimension des Arrays angepasst (ReDim Preserve arrSchicht(366, iZ)).
Die Überschriften stehen dann hier unter arrSchicht(0,0) bis arrSchicht(366,0). Die Daten vom ersten Mitarbeiter sind unter arrSchicht(0,1) bis arrSchicht(366,1) gespeichert, die vom zweiten unter arrSchicht(0,2) bis arrSchicht(366,2) usw.
Wenn hier noch zusätzliche Daten (Name, Vorname usw.) gespeichert werden sollen, muss das Array in der ersten Dimension halt entsprechend größer werden.
Beispiel:
ReDim Preserve arrSchicht(368, 0): würde eine Array mit 369 Spalten erstellen.
arrSchicht(0, 0) könnte dann die PersonalNr., arrSchicht(1, 0) den Namen, arrSchicht(2, 0) den Vornamen und arrSchicht(3, 0) bis arrSchicht(369, 0) die einzelnen Tage aufnehmen.

Gruß
Robert


  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Jonas Albrecht
Geschrieben am: 17.04.2018 12:34:21

Hallo Robert ich weiß deine Hilfe sehr zu schätzen.

Ich habe mir noch einmal Gedanken über die Strukturierung gemacht und mir ein paar Änderungen überlegt. Das File dazu findest du unten. Ich habe das Tabellenblatt "Anwesenheit" erstellt. In dieses werden mittels VBA die im Moment noch fiktiven Mitarbeiterdaten (Personalnr, Nachname, Vorname, AusweisNr. und Stamm/LAK übertragen. Diese Daten kommen über VBA aus den Accessdatenbank-Abfragen die auf den Tabellenblättern "D1" und "D2" zu finden sind. Ab der Spalte AJ findest du einen vorgetragenen Kalender und ich habe per Hand exemplarisch ein Schichtmodell eingetragen. Das kann man später eventuell auch noch aus der Datenbank abfragen. Fürs erste möchte ich jedoch, dass wenn es zu Änderungen am Schichtmodell einzelner Mitarbeiter kommt, das bestehende Modell überschrieben wird. Im Tabellenblatt "Accessdaten2" ist dafür das Ergebnis einer Abfrage aus Access eingefügt. Der Mitarbeiter mit der Nummer 0002 soll vom 01.01.2018 bis zum 05.01.2018 in der Spätschicht arbeiten. Laut Anwesenheitstabelle hat er jedoch Frühschicht. Das F für früh soll jetzt durch ein S für Spät ersetzt werden. Das Makro müsste also im Tabellenblatt "Anwesenheit" die Spalte der Personalnummern durchsuchen und im Kalender die Schicht ersetzen. Wichtig ist auch das alles funktioniert, sollte es sich bei der Änderung nicht um die ganze Woche handeln sondern nur ein paar Tage. Ich weiß ich verlange viel, aber vllt kannst du mir ja einen Ansatz zur Lösung geben.

https://www.herber.de/bbs/user/121093.zip


  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Robert Werner
Geschrieben am: 18.04.2018 18:06:24

Hallo Jonas,

nachstehendes Makro müsste die Änderungen einbauen:

Sub SchichtplanAktualsieren()
Dim wksSchichtNeu As Worksheet, wksAnwesennheit As Worksheet
Dim cl As Range, lZ As Long
Dim lPersNr, iDat

Set wksSchichtNeu = Sheets("AccessDaten2")
Set wksAnwesennheit = Sheets("Anwesenheit")

lZ = wksSchichtNeu.Range("A" & Rows.Count).End(xlUp).Row
For Each cl In wksSchichtNeu.Range("A2:A" & lZ).Cells
    lPersNr = Application.Match(cl, wksAnwesennheit.Range("C:C"), 0)
    If IsError(lPersNr) Then
        MsgBox "Die PersonalNr. " & cl & " wurde in der Tabelle " & wksAnwesennheit.Name & "  _
nicht gefunden!", vbCritical, "Fehler"
        Exit Sub
    End If
    iDat = Application.Match(cl.Offset(0, 2), wksAnwesennheit.Range("5:5"), 0)
    If IsError(iDat) Then
        MsgBox "Das Datum " & cl & " wurde in der Tabelle " & wksAnwesennheit.Name & " nicht  _
gefunden!", vbCritical, "Fehler"
        Exit Sub
    End If
    Do
       wksAnwesennheit.Cells(lPersNr, iDat) = cl.Offset(0, 1)
       iDat = iDat + 1
    Loop While cl.Offset(0, 3) >= wksAnwesennheit.Cells(5, iDat)
Next

End Sub

Gruß
Robert


  

Betrifft: AW: Formatierung einer aus Access importierten Tabelle von: Jonas Albrecht
Geschrieben am: 19.04.2018 10:58:37

Super funktioniert hervorragend!! Tausend Dank!


Beiträge aus dem Excel-Forum zum Thema "Formatierung einer aus Access importierten Tabelle"