VBA aus zwei Dateien vergleichen und kopieren?

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Computer, programmieren
26.07.2019, 09:23

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.

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?
0
@Blacckoutbash

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.
1

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?
0
@Blacckoutbash

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.
1
@daCypher

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?
0
@Blacckoutbash

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
1
@daCypher

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)
0
@Blacckoutbash

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.
1
@daCypher

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.
0
@Blacckoutbash

Ersetze mal die Zeile 

If Not foundStammCell = Nothing Then

durch

If foundStammCell IsNot Nothing Then

oder durch

If Not foundStammCell Is Nothing Then
1
@daCypher

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
0
@Blacckoutbash

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?
1
@daCypher

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)
0
@daCypher

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?
0
@Blacckoutbash

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.
2
@daCypher

Hallo Danke für die Antwort, wenn ich deinen neuen Code 1:1 verwende wird nun leider gar nichts mehr kopiert und eingefügt.
0
@Blacckoutbash

Oh, stimmt, ich hab vergessen, das "Not" zu löschen bei "If Not foundStammCell...."
1
@Blacckoutbash

ja, wenn alles so funktioniert, wie es soll, werden sie jedes mal überschrieben.
1
@daCypher

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
0
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Computer, Technik
26.07.2019, 08:41

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...
Woher ich das weiß:Studium / Ausbildung – Informatiker
25.07.2019, 15:55

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?
1
@Dudenox

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...
1
@Blacckoutbash 
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)
0

Wenigstens die Dimension des Zellbereichs müsste gleich sein.
0