VBA aus zwei Dateien vergleichen und kopieren?
Hallo ich habe gerade folgende Situation:
Ich habe zwei Dateien die identisch Aufgebaut sind.
Datei1 wird auf SAP exportiert und sieht wie folgt aus: Spalte B User 1 - 80; Spalte M Anmeldedaten für die jeweiligen User.
Datei2 auf dem PC als Übersicht: Spalte F User1-80 und Spalte M die Anmeldedaten welche aus der exportierten Datei eingetragen und aktualisiert werden sollen. Nun kann es sein dass aus Datei ein zb User 2 oder 33 etc fehlt, deshalb soll er mit datei 2 vergleichen und zu den richtigen Usern die Anmeldedaten eintragen.
Ich habe da schon folgendes Programmiert aber es wird nichts eingetragen ich hoffe ihr könnt mir helfen:
Dim aktuelleZelle As Range
Stamm = datei2
Quelldatei = datei1
For Each aktuelleZelle In Sheets("SAP-Abfrage_GEN-User_SUIM.xls")
If datei1.Sheets("SAP-Abfrage_GEN-User_SUIM.xls").Range("M29:M105") = datei2.Sheets("Tabelle1").Range("M2:M81") Then
Workbooks(Quelldatei).Sheets("SAP-Abfrage_GEN-User_SUIM").Range("M29:M105").Copy
Workbooks(Stamm).Sheets("Tabelle1").Range("M2:M81").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
Next aktuelleZelle
3 Antworten
Da gibt es ein paar Sachen, die mir noch nicht klar sind. Einerseits was für ein Datentyp sind "Stamm", "Quelldatei", "datei1" und "datei2"? Die benutzt du unterschiedlich. Einerseits benutzt du "datei1" und "datei2" so, als wenn es Workbook-Objekte wären (datei1.Sheets... ), andererseits benutzt du "Stamm" und "Quelldatei" so, als wenn es Strings wären (Workbooks(Quelldatei)...)
Mein Tipp: Schreibe in die erste Zeile vom Code immer "Option Explicit", dann sagt VBA dir bescheid, wenn es irgendwelche Variablen nicht kennt.
Nächster Punkt: Du lässt den Code für jede Zelle aus der Tabelle "SAP-Abfrage_GEN-User_SUIM.xls" laufen (also nicht nur für B1:B80 oder M1:M80).
Jedes mal, wenn die Schleife durchläuft, vergleichst du zwei unterschiedlich große Range-Objekte aus den zwei Workbooks. Ich denke mal, die Bedingung wird immer falsch sein.
Jedes Mal, wenn die Bedingung wahr ist, kopierst du den Bereich M29:M105 aus dem einen Workbook in den Bereich M2:M81 des anderen Workbooks. Spätestens hier würde VBA eine Fehlermeldung ausgeben, weil die Bereiche nicht gleich groß sind. Außerdem macht es so keinen Sinn, weil die Benutzer evtl. nicht gleich sortiert sind.
Abgesehen davon, dass die Aufgabe mit einem Datenbankprogramm in zwei Minuten erledigt wäre (einfach eine Anfügeabfrage bauen) und ich nicht verstehe, warum du Login-Daten von irgendwem in eine Excel-Tabelle speichern willst (ich hoffe mal, die Passwörter sind wenigstens verschlüsselt), versuch ich mal, dir so zu helfen, wie ich die Aufgabe verstanden hab.
Also du hast eine Datei aus SAP (ich nenne sie mal SAP.xls) und eine Datei mit Stammdaten (die nenne ich jetzt mal Stamm.xls). Alle Einträge aus SAP.xls, die in Stamm.xls fehlen, willst du jetzt an Stamm.xls anfügen.
Option Explicit
Public Sub test()
Dim wbSAP As Workbook
Dim wbStamm As Workbook
Dim sapSheet As Worksheet
Dim stammSheet As Worksheet
Dim sapRange As Range
Dim stammRange As Range
Dim curSapCell As Range
Dim foundStammCell As Range
Dim nextStammRowNr As Integer
Set wbSAP = Workbooks.Open("C:\SAP.xls")
Set wbStamm = Workbooks.Open("C:\Stamm.xls")
Set sapSheet = wbSAP.Sheets("SAP-Abfrage_GEN-User_SUIM.xls")
Set stammSheet = wbStamm.Sheets("Tabelle1")
Set sapRange = sapSheet.Range("B29:B105")
Set stammRange = stammSheet.Range("F2:F81")
nextStammRowNr = stammRange.End.Row + 1
For Each curSapCell In sapRange
foundStammCell = stammRange.Find(curSapCell.Value, LookAt:=xlWhole)
If foundStammCell = Nothing Then
stammSheet.Range("B" & nextStammRowNr) = curSapCell.Value
stammSheet.Range("M" & nextStammRowNr) = sapSheet.Range("M" & curSapCell.Row).Value
nextStammRowNr = nextStammRowNr + 1
' Else
' stammSheet.Range("M" & foundStammCell.Row) = sapSheet.Range("M" & curSapCell.Row).Value
End If
Next curSapCell
End Sub
Falls auch die vorhandenen Daten aktualisiert werden sollen, kannst du die beiden Hochkommas bei den auskommentierten Zeilen löschen.
Ich hab den Code nicht getestet, aber falls Fehler auftreten, kriegst du die wahrscheinlich besser glattgebügelt, als bei deinem Code.
Wie gesagt, ich würde sowas eher in einer Datenbank machen. Dafür sind Datenbanken da.
Der Code geht jede Zeile in der SAP-Tabelle durch und sucht die Usernamen in der Stamm-Tabelle. Wenn er den Usernamen in der Stamm-Tabelle nicht findet, kopiert er den Usernamen und das dazugehörige Kennwort als neue Zeile in die Stamm-Tabelle. Optional kannst du auch die beiden Hochkommas beim Else wegmachen, dann kopiert er das Passwort bei den gefundenen Usernamen.
Wie mache ich das denn dass er die Anmeldedaten aus der SAP datei in die Spalte zu den Usernamen in der anderen Datei kopiert, denn die Usernamen stehen schon eine Spalte daneben drin?
Also quasi so, dass er gar nichts macht, wenn er den Username nicht findet, aber nur das Passwort kopiert, wenn er ihn findet? Dafür musst du nur das If ändern.
If Not foundStammCell = Nothing Then
stammSheet.Range("M" & foundStammCell.Row) = sapSheet.Range("M" & curSapCell.Row).Value
End If
Genau er soll nur die Usernamen vergleichen und aus der SAP tabelle das dazugehörige Anmeldedatum herausnehmen und dann in meine Stammtabelle (die sowas wie ein übersicht ist) in die Spalte zu den richtigen Usernamen reinkopieren. (Übrigend mit Anmeldedaten sind keine Passwörter gemeint, sondern wann sich welcher User zuletzt angemeldet hat.) Was ich auch nicht genau verstehe ist, was du meinst, dass ich die nächste freie Zeile selber eintragen soll bei nextStammRowNr? (in meinem Fall die 82)
Habe dir mal zur besseren Verständnis ein Bild erstellt: https://imgur.com/JFwdZoq so ungefähr ist das aufgebaut, nur mit viel mehr Usern und dass es eben zwei Dateien ist.
Achso, ich hab das andersrum verstanden. Ich dachte, in der SAP-Tabelle sind mehr Einträge drin, als in der Stamm-Tabelle. Dann hätte er halt alle Benutzer, die in der SAP-Tabelle, aber nicht in der Stamm-Tabelle stehen, in die Stamm-Tabelle eingefügt. Da hättest du dann halt statt "nextStammRowNr = stammRange.End.Row + 1" einfach fest die 82 reinschreiben sollen, also "nextStammRowNr = 82". Im Prinzip kannst du die Zeile aber komplett rauslöschen, wenn du die neue Variante von der If-Abfrage benutzt.
Vielen Dank für deine Anwort nun bekomme ich jedoch bei Nothing "Unzulässige Verwendung eines Obejekts" Mein Code schaut nun wie folgt aus.
For Each curSapCell In sapRange
foundStammCell = stammRange.Find(curSapCell.Value, LookAt:=xlWhole)
If Not foundStammCell = Nothing Then
stammSheet.Range("M" & nextStammRowNr) = sapSheet.Range("M" & curSapCell.Row).Value
End If
Next curSapCell
Das mit den Usern kann eben immer unterschiedlich sein, kann sein dass alle User da sind kann aber auch sein dass nur einer fehlt. Deswegen wollte ich dass er die Namen vergleicht Anmeldedatum nimmt und dann in meiner Stammdatei wieder zum richtigen User hinzufügt.
Ersetze mal die Zeile
If Not foundStammCell = Nothing Then
durch
If foundStammCell IsNot Nothing Then
oder durch
If Not foundStammCell Is Nothing Then
Hallo ich bedanke mich aufjedenfall für deine ausführliche Hilfe und dass du dir die Zeit nimmst und es mit mir aushältst. Jetzt bekomme ich beim debuggen nach Set stammSheet = wbStamm.Sheets("Tabelle1") den Fehler Objektvariable oder With Blockvariable nicht festgelegt
Bitteschön, kein Problem.
Die Fehlermeldung heißt normalerweise, dass ein Objekt nicht festgelegt ist. Hast du die Zeile Set wbStamm = Workbooks.Open("C:\Stamm.xls") wieder rausgenommen? Oder hast du vielleicht die Tabelle1 da drin umbenannt?
Ich habe set wbStamm nun als ThisWorkbook definiert, da es sowieso immer von dort geöffnet wird. Habe nun eher das Problem dass er beim Kopieren in die komplette Spalte reinkopiert und nicht zu den Usern, weißt du eventuell warum? (Habe dein Code verwendet vielen vielen Dank nochmal)
Also es wird zwar was kopiert, jedoch stehen dann auch Daten bei Usern drin die in der SAP Datei eigentlich gar nicht da sind. Z.B fehlt User 1in der SAP Datei, jedoch wird mir in meiner Stammdatei bei User1 ein Datum reinkopiert?
Ich hab mir grade mal den Code angeschaut, den du bei deiner letzten Frage reingeschrieben hast. Da hast du den Teil, der ausgeführt werden soll, wenn der Benutzer nur in der SAP-Tabelle gefunden wird, nur so halb rausgelöscht. Dadurch, dass die "nextStammRowNr" auch nicht mehr gefüllt wird, fängt er da einfach bei 0 an. Also wenn ein Benutzer in der Stamm-Tabelle nicht gefunden wird, wird das Datum dazu in die erste Zeile kopiert. Ich hab nochmal ein paar Änderungen daran vorgenommen:
Dim wbSAP As Workbook
Dim wbStamm As Workbook
Dim sapSheet As Worksheet
Dim stammSheet As Worksheet
Dim sapRange As Range
Dim stammRange As Range
Dim curSapCell As Range
Dim foundStammCell As Range
Set wbSAP = Workbooks.Open(ThisWorkbook.Path & "\SAP-Abfrage_GEN-User_SUIM.xls")
Set wbStamm = ThisWorkbook
Set sapSheet = wbSAP.Sheets("SAP-Abfrage_GEN-User_SUIM")
Set stammSheet = wbStamm.Sheets("Tabelle1")
Set sapRange = sapSheet.Range("B29:B105")
Set stammRange = stammSheet.Range("F2:F81")
For Each curSapCell In sapRange
foundStammCell = stammRange.Find(curSapCell.Value, LookAt:=xlWhole)
If Not foundStammCell.Value = curSapCell.Value Then
stammSheet.Range("M" & foundStammCell.Row) = sapSheet.Range("M" & curSapCell.Row).Value
End If
Next curSapCell
Der Teil, wo das stammSheet eingestellt wird, sollte eigentlich so funktionieren. Ansonsten kannst du es auch einfach auf "ActiveSheet" setzen.
Hallo Danke für die Antwort, wenn ich deinen neuen Code 1:1 verwende wird nun leider gar nichts mehr kopiert und eingefügt.
Oh, stimmt, ich hab vergessen, das "Not" zu löschen bei "If Not foundStammCell...."
ja, wenn alles so funktioniert, wie es soll, werden sie jedes mal überschrieben.
Hallo, ich bastle an einem Addin der ähnlich funktionieren sollte. Ich habe den oberen Code eingegeben doch bei mir kommt bei 'foundStammCell = stammRange.Find(curSapCell.Value, LookAt:=xlWhole) die Fehlermeldung: Objektvariable oder With oder Blockvariable nicht festgelegt.
Danke im Voraus
Hallo vielen Dank für deine Antwort hat mir sehr geholfen, ich bekomme bei nextStammRowNr = stammRange.End.Row + 1 die Fehlermeldung Argument ist nicht Optional, weißt du an was das liegen kann?
Stimmt, ich hab die Zeile grade mal ausprobiert. Die Funktion "End" braucht einen Parameter, in welche Richtung man das Ende sucht. Leider bringt die Funktion aber nicht das Ergebnis, was ich erwartet habe, deshalb wäre es wohl besser, wenn du da einfach per Hand die nächste freie Zeilennummer (in deinem Fall also 82) reinschreibst.
Ich würde da der Einfachheit halber in der Tabelle 1 in eine zusätzlichen Spalte einen SVERWEIS auf Tabelle 2 einfügen =SVERWEIS(B1;Tabelle2!$M:$M;1;falsch) und die Formel in der Spalte in alle Zeilen kopieren. Überall, wo #NV drin steht, gibt es den Wert in Tabelle 2 nicht...
Du überprüfst für jede aktuelle Zeile auf die selbe Range, ob die identisch ist. Das macht keinen Sinn.
Stell dir vor du baust eine Mauer und willst nun überprüfen, ob die Mauer daneben gleiche Steine verwendet, aber für jeden Stein vergleichst du die komplette Mauer A mit der kompletten Mauer B.
Verstehe, wie vergleiche ich denn die einzelnen inhalte?
Ne vermutlich nicht, ich bin leider noch ein ziemlicher Anfänger was VBA angeht, ich bin schon am zweifeln wie ich denn die Spalte durchlaufe und jeweils die Inhalte miteinander vergleiche...
bGleich = True
For i = 2 To 81
If Bla.Range("M"&i).Value <> Blub.Range("M"&(i+27)).Value Then
bGleich = False
Exit For
End If
Next
(ungetestet)
Soweit ich dein Code verstehe vergleicht er ja nur die Usernamen und kopiert diese, es sollte jedoch die Usernamen vergleichen und die Anmeldedaten in Spalte M kopieren und in der anderen Tabelle zu den richtigen Usernamen einfügen. Oder verstehe ich dein Code falsch?