Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formatierung einer aus Access importierten Tabelle

Formatierung einer aus Access importierten Tabelle
12.04.2018 10:02:08
Jonas
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

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formatierung einer aus Access importierten Tabelle
12.04.2018 15:59:35
Robert
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
Anzeige
AW: Formatierung einer aus Access importierten Tabelle
12.04.2018 16:19:04
fcs
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  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

Anzeige
AW: Formatierung einer aus Access importierten Tabelle
16.04.2018 14:00:04
Jonas
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?
AW: Formatierung einer aus Access importierten Tabelle
16.04.2018 20:45:25
Robert
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
Anzeige
AW: Formatierung einer aus Access importierten Tabelle
17.04.2018 12:34:21
Jonas
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
Anzeige
AW: Formatierung einer aus Access importierten Tabelle
18.04.2018 18:06:24
Robert
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
Anzeige
AW: Formatierung einer aus Access importierten Tabelle
19.04.2018 10:58:37
Jonas
Super funktioniert hervorragend!! Tausend Dank!

308 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige