PDA

Vollständige Version anzeigen : Nach Aktualisierung Zeilen Anpassung vornehmen


Showd0wn
13.07.2014, 08:30
Hallo,

kurz mein Problem:
Ich habe eine Quelldatei aus der ich einige Daten in mein neues Excel File per VBA kopiere (Ich habe die komplette Range der Quelldatei als Variant gespeichert und lese in meinem neuen File dann die Spalten ein, die ich brauche). Diese Quelldatei ändert sich immer und ich kann die Daten in meinem neuen File dann immer aktualisieren - so weit läuft alles.
Jetzt gibt es aber die Möglichkeit in der neuen Datei noch Kommentare zu den einzelnen Zeilen hinzuzufügen, also quasi eine neue Spalte, die es in der Quelldatei nicht gibt.
Wenn ich jetzt meine Daten aktualisiere, kann es natürlich vorkommen, dass Zeilen gelöscht wurden und dann muss die Kommentarspalte entsprechend angepasst werden.
Wenn beispielsweise eine Zeile in der Quelldatei gelöscht wurde, und ich dann in meiner neuen Datei aktualisiere, müssen alle Kommentare ab der gelöschten Zeile um eins nach oben verschoben werden.

Wie setzte ich das in VBA um?

Gruß

aloys78
13.07.2014, 09:29
Hallo,

gibt es in den Zeilen Schlüsselbegriffe, wie zB Artikel-Nr, Personal-Nr oder Namen, die die Zeilen eindeutig gegeneinander unterscheidbar machen ?

Dann könntest du in den neuen Daten über diesen Schlüssel die Kommentare aus dem alten Bestand übernehmen.

Gruß
Aloys

Showd0wn
13.07.2014, 10:25
oh ja, das habe ich vergessen, sry. Ja die gibt es. Über Werte aus 2 Spalten lassen sich die Zeilen sich eindeutig identifizieren.
Aber wie setzte ich diese Schlüsselung in VBA um?

aloys78
13.07.2014, 10:46
Hallo,
Aber wie setzte ich diese Schlüsselung in VBA um?
Lade doch mal Beispiel-Dateien mit dem vorhandenen Code hoch, dann kann ma leichter einen geeigneten Vorschlag unterbreiten.

Wie geht hoch laden ?
Unterhalb des Antwortblockes unter "Zusätzliche Einstellungen" kannst du bei "Anhänge verwalten" solche anhängen.

Gruß
Aloys

Showd0wn
13.07.2014, 15:41
Hallo,
habe den Code gerade nicht zur Verfügung. Aber das sieht in etwa so aus (ein wenig vereinfacht):

Sub aktualiseren()

Dim varQuelle As Variant
Dim x, i As Integer

Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Quelle.xlsx"

x = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
varQuelle = ActiveWorkbook.Sheets(1).Range("A1:E" & x)
ThisWorkbook.Activate

For i = 1 To x
ThisWorkbook.Sheets(1).Cells(i, 1) = varQuelle(i, 1) 'Spalte 1
ThisWorkbook.Sheets(1).Cells(i, 3) = varQuelle(i, 3) 'Spalte 3
ThisWorkbook.Sheets(1).Cells(i, 4) = varQuelle(i, 4) 'Spalte 4
Next

'in Spalte 5 steht dann ein Kommentar, der beim Aktualisieren angepasst werden soll

End Sub


Über die Spalten 1 & 3 wäre dann die Zeile z.B. eindeutig zu identifizieren.

aloys78
13.07.2014, 17:40
Hallo,
Über die Spalten 1 & 3 wäre dann die Zeile z.B. eindeutig zu identifizieren.
Und in welchem Blatt befinden sich die alten Daten.

Außerdem hatte ich empfohlen, Beispiel-Tabellen zur Verfügung zu stellen. Die machen Sinn, um einen Code-Vorschlag zu testen.

Gruß
Aloys

Showd0wn
14.07.2014, 01:12
habe mal 2 kurze Beispielsdateien hochgeladen, nur um das mal zu verdeutlichen.
Was meinst du?

aloys78
14.07.2014, 06:52
Hallo,
habe mal 2 kurze Beispielsdateien hochgeladen, nur um das mal zu verdeutlichen.
Verdeutlichen - das ist übertrieben.

Aus den sogenannten Beispieldateien kann man zB nicht erkennen, ob das der Zustand vor oder nach dem Kopieren ist.
Wenn das typische Beispieldaten sein sollten, dann werden die alten Daten für jeden Schlüssel durch die Quelldaten ersetzt; dann hast du aber kein Problem mit den Kommentaren.
Nach deinem ersten Beitrag wird aber der alte Bestand nur durch einige neue Daten aktualisiert. Dies steht im Widerspruch zu deinen Beispieldaten und zu deinem Code. Der aktualisiert den alten Bestand nicht Schlüsselgetreu, sondern überschreibt einfach nur.

Zusammgefasst: präsentiere aussagekräftige Beispieldatein mit Status vorher / nachher, aus denen man klar erkennen kann, was du möchtest.

Gruß
Aloys

Showd0wn
14.07.2014, 09:50
Ok, dann hier nochmal ein bisschen schöner.
Wenn du die Aktualisierung Datei aufrufst, und aktualisieren klickst, werden die Daten aus Quelle geholt. Dort habe ich die Zeile mit "Nr. 3" gelöscht, d.h. in der Aktualisieren-Datei ist sie momentan noch enthalten, aber wenn die neuen Aktualisierungen geladen werden gibt es die Zeile mit Nr.3 nicht mehr.
Das mit dem Anpassen habe ich bereits ein bisschen hingebekommen. Die Zeilen stimmen jetzt. Aber wie du sehen wirst, wird der Kommentar von Nr.3, die ja gelöscht wurde, in die letzte Zeile übertragen. Wie kann ich das vermeiden bzw. den Kommentar dann löschen?

Showd0wn
19.07.2014, 18:13
jemand ne Idee?

aloys78
21.07.2014, 08:48
Hallo Showdown,
jemand ne Idee?
Wozu ?
Ok, dann hier nochmal ein bisschen schöner.
Wenn du die Aktualisierung Datei aufrufst, und aktualisieren klickst, werden die Daten aus Quelle geholt. Dort habe ich die Zeile mit "Nr. 3" gelöscht, d.h. in der Aktualisieren-Datei ist sie momentan noch enthalten, aber wenn die neuen Aktualisierungen geladen werden gibt es die Zeile mit Nr.3 nicht mehr.
Es geht nicht um schöner, sondern um klarer.
Aber eine einfache und nachvollziehbare Beschreibung für dein Problem liegt immer noch nicht vor. Und aufs Knöpfchen drücken und dann mühsam nachzuvollziehen, was sich da geändert haben könnte, ist auch kein Ersatz hierfür.
In deinem Fall bietet es sich, anhand von wenigen Beispieldaten aufzuzeigen und zu erläutern: Quelldaten, Ziel-Tabelle vorher und nachher, Besonderheiten beim Update etc

Gruß
Aloys

Showd0wn
28.07.2014, 13:00
Die ersten 4 Spalten werden aus der Quell-Datei übernommen. Anhang von Nummer & Summe, kann die Zeilen eindeutig identifiziert werden. Die letzte Spalte "Kommentar" wird manuell in der Aktualisieren-Datei vom Benutzer eingegeben und gehört genau zu der einen Zeile, d.h. wird eine Zeile davor gelöscht und die Zeile rückt eins nach oben, so soll auch der Kommentar eins nach oben rücken.

Okay, dann nochmal klarer mit Bildern:
Das ist meine Ausgangsdatei (die, in der die Aktualisierungen stattfinden)
http://i57.tinypic.com/bhkqqg.png

wird eine Zeile eingefügt, passt alles:
http://i62.tinypic.com/23hkboo.png

dann werden 2 Zeilen gelöscht, auch alles gut:
http://i60.tinypic.com/1zxrvrb.png

jetzt wird es problematisch: es wird die erste Zeile gelöscht (1/10) und eine neue am Ende eingefügt. Dabei wird allerdings der Kommentar der letzten Zeile übernommen, obwohl das Feld eigentlich leer sein sollte:
http://i58.tinypic.com/2uhlk3o.png

Das Problem bleibt auch immer gleich. Hier wird nochmal ausgehend vom ersten Screenshot, die erste Zeile (1/10) wieder gelöscht und 2 neue eingefügt. Dabei wird wieder in der ersten neuen Zeile (4/10) der Kommentar der letzten alten Zeile (3/20) übernommen:
http://i57.tinypic.com/21ccaj6.png

Hier nochmal das gleiche: die ersten beiden Zeilen werden gelöscht (vom ersten Screenshot) & drei neue werden eingefügt:
http://i58.tinypic.com/2me4868.png


Hier ist nochmal mein Code (wurde seit dem Upload der Datein, noch ein wenig angepasst):
Option Explicit

Sub aktualisieren()
Dim i, j, k As Integer
Dim length_q, length_akt, length_akt_2 As Long
Dim var_q, var_akt, var_akt_2 As Variant
Dim wksDaten As Worksheet
Dim s As String

s = ThisWorkbook.Path & Application.PathSeparator & "Quelle.xlsx"

Set wksDaten = Workbooks.Open(s).Sheets("Ausgang")

length_q = wksDaten.Cells(Rows.Count, 3).End(xlUp).Row
var_q = wksDaten.Range("A9:H" & length_q).Value
length_akt = Tabelle1.Cells(Rows.Count, 2).End(xlUp).Row
var_akt = Tabelle1.Range("B4:F" & length_q).Value

ThisWorkbook.Activate

Sheets("Tabelle1").Select
Range("B4:F" & length_akt).ClearComments

j = 4
For i = 1 To (length_q - 8)
Tabelle1.Cells(j, 2).Value = var_q(i, 3) 'Nr.
Tabelle1.Cells(j, 3).Value = var_q(i, 4) 'Summe
Tabelle1.Cells(j, 4).Value = var_q(i, 5) 'Info
Tabelle1.Cells(j, 5).Value = var_q(i, 6) 'Weiteres
j = j + 1
Next

length_akt_2 = Tabelle1.Cells(Rows.Count, 2).End(xlUp).Row
var_akt_2 = Tabelle1.Range("B4:F" & length_q).Value

For j = 1 To (length_akt - 3)
For k = 1 To (length_akt_2 - 3)
If var_akt(j, 1) = var_akt_2(k, 1) Then 'Nr.
If var_akt(j, 2) = var_akt_2(k, 2) Then 'Summe
Tabelle1.Cells(k + 3, 6).Value = var_akt(j, 5) 'Kommentar
End If
End If

Next
Next

If (length_akt_2 - 3) > (length_q - 8) Then
Dim loeschen, m, n As Integer
loeschen = (length_akt_2 - 3) - (length_q - 8)
For m = 1 To loeschen
For n = 2 To 6
Tabelle1.Cells((length_akt_2 - m + 1), n) = ""
Tabelle1.Cells((length_akt_2 - m + 1), n) = ""
Tabelle1.Cells((length_akt_2 - m + 1), n) = ""
Tabelle1.Cells((length_akt_2 - m + 1), n) = ""
Tabelle1.Cells((length_akt_2 - m + 1), n) = ""
Next
Next
End If

wksDaten.Parent.Close False

End Sub


Aufbau:
1. Dateien werden geladen und die entsprechende Range wird von der Quelle in die Aktualisieren-Datei geladen
2. Die alte Range der Aktualisieren-Datei wird mit der neuen Range der Aktualisieren-Datei verglichen & die Kommentare werden angepasst.
3. Die überflüssigen Zeilen werden entfernt.

Wie man sieht liegt das Problem an dem letzten Kommentaren. Wenn z.B. zwei Zeilen gelöscht werden & mehrere Zeilen eingefügt werden, werden die Kommentare der zwei gelöschten Zeilen nicht gelöscht, sondern in die neuen übernommen.
Wie kann ich das Problem lösen?

aloys78
28.07.2014, 17:46
Hallo,

wenn ich deinen Code richtig interpretiere, dann werden die Daten in den Spalten D bis E der Tabelle1 durch die Quelldaten jeweils komplett ersetzt. In bestimmten Konstellationen passt dann der eingetragene Kommentar dann nicht mehr zu den Daten.

Für diesen Fall anbei ein Code-Vorschlag, der die Kommentare bei Änderungen den ursprünglichen Zeilen wieder zuordnet.
Option Explicit

Sub aktualisieren()
Dim ws_Q As Worksheet 'Quell-Tabelle
Dim ws_Z As Worksheet 'Ziel-Tabelle
Dim s As String 'Pfad Quelldatei
Dim q As Long 'Zeilen# Quell-Tabelle
Dim z As Long 'Zeilen# Ziel-Tabelle
Dim c As Long 'Spalten#
Dim LoL_Q As Long 'letzte Zeile Quell-Tabelle
Dim LoL_Z As Long 'Letzte Zeile Ziel-Tabelle
Dim arr_K1() 'Array zur Sicherung der Kommentare (Key)
Dim arr_K2() 'Array zur Sicherung der Kommentare (Kommentar)
Dim a As Long 'Index Array
Dim Such 'Vergleichsbegriff
Dim erg 'Ergebnis Vergleich

'Initialisierung
s = ThisWorkbook.Path & Application.PathSeparator & "Quelle.xlsx" 'Pfad Quell-Datei
Set ws_Q = Workbooks.Open(s).Sheets("Ausgang") 'Quell-Tabelle
LoL_Q = ws_Q.Cells(Rows.Count, "C").End(xlUp).Row 'letzte Zeile Quell-Tabelle
Set ws_Z = ThisWorkbook.Worksheets("Tabelle1") 'Ziel-Tabelle

'Sichern Kommentare Ziel-Datei in Arrays
With ws_Z
LoL_Z = .Cells(Rows.Count, "B").End(xlUp).Row 'letzte Zeile
ReDim arr_K1(1 To LoL_Z - 3) 'Array Keys
ReDim arr_K2(1 To LoL_Z - 3) 'Array Kommentare
For z = 4 To LoL_Z
a = z - 3 'Index Arrays
arr_K1(a) = .Range("B" & z) & "_" & .Range("C" & z) 'Nummer & "_" & Summe = Key
arr_K2(a) = .Range("F" & z) 'Kommentar
Next z

'Leeren Datenbereich in Ziel-Tabelle
.Range("B4:F" & LoL_Z).ClearContents

'Kopieren Quelldaten nach Ziel-Tabelle
Application.ScreenUpdating = False
z = 3
For q = 9 To LoL_Q
z = z + 1 'Zeilen# Ziel-Tabelle aktualisieren
For c = 3 To 6 'Kopieren nacheinander Nummer, Summe, Infos, Weiteres
.Cells(z, c - 1) = ws_Q.Cells(q, c)
Next c
Next q

'Ergänzen Ziel-Daten um Kommentare
LoL_Z = .Cells(Rows.Count, "B").End(xlUp).Row 'letzte Zeile der neuen Ziel-Tabelle
For z = 4 To LoL_Z
Such = .Range("B" & z) & "_" & .Range("C" & z)
erg = Application.Match(Such, arr_K1, 0)
If IsNumeric(erg) Then .Range("F" & z) = arr_K2(erg)
Next z
Application.ScreenUpdating = True
End With

'Quelldatei schließen
ws_Q.Parent.Close False
End Sub


Gruß
Aloys

Showd0wn
29.07.2014, 22:32
Vielen Dank für deine Arbeit!

Aktuell habe ich allgemein das Problem, das mit ganz am Anfang beim initialisieren auf einmal ein Laufzeitfehler 6 Überlauf angezeigt wird, wenn ich die Range laden will (var_q = wksDaten.Range("A9:H" & length_q).Value). weißt du vielleicht woran das liegen könnte?

aloys78
30.07.2014, 06:29
Hallo,
Aktuell habe ich allgemein das Problem, das mit ganz am Anfang beim initialisieren auf einmal ein Laufzeitfehler 6 Überlauf angezeigt wird, wenn ich die Range laden will (var_q = wksDaten.Range("A9:H" & length_q).Value). weißt du vielleicht woran das liegen könnte?
Sorry - das ist dein Code.
Wenn du meinen Lösungsvorschlag schon nicht übernehmen willst, dann fände ich es als fair, wenn du das mit einem Kommentar klar kommunizieren würdest.

VG
Aloys