Office: Access Tabelle mit Excel Daten updaten

Hallo zusammen,
ich habe eine Excel Arbeitsmappe die ständig aktualisiert wird. Die Spaltennamen stimmen mit den Spaltennamen der Tabelle in Access überein. Jetzt möchte ich gerne einen VBA Code für Access schreiben mit dem ich per Knopfdruck die Werte aus Access bei Ungleichheit mit den Werten aus Excel update/überschreibe. Außerdem sollen nach Möglichkeit Werte die es in Access nicht nicht aber in Excel, in der Access Tabelle hinzugefügt werden, und umgekehrt.
Ich habe bisher den folgenden Code, jedoch ist in diesem Code der letzte Teil meiner Frage nicht vorhanden, sowie wird mir eine Fehlermeldung "Benutzerdefinierter Typ nicht definiert" angezeigt.

Code:

Sub Datenvergleich()

    'Deklaration der Variablen
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim rng As Excel.Range
    Dim accApp As Access.Application
    Dim accDB As Access.Database
    Dim accRec As Access.Recordset
    Dim accSQL As String
    Dim accID As Long
    
    'Öffnen der Excel-Datei und des Arbeitsblatts
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Pfad\zur\Datei\ArtikelListe1.xlsx")
    Set xlWS = xlWB.Worksheets("Artikel")
    
    'Öffnen der Access-Datenbank und der Tabelle
    Set accApp = New Access.Application
    accApp.OpenCurrentDatabase "C:\Pfad\zur\Datei\Test_Datenbank.accdb"
    Set accDB = accApp.CurrentDb
    Set accRec = accDB.OpenRecordset("tblArtikel")
    
    'Vergleich der Datensätze in der Excel-Tabelle mit der Access-Tabelle
    For Each rng In xlWS.Range("A2", xlWS.Range("A2").End(xlDown))
        accSQL = "SELECT * FROM tblArtikel WHERE Artikelnummer = " & rng.Value
        accRec.FindFirst accSQL
        If Not accRec.NoMatch Then
            If rng.Offset(0, 1).Value <> accRec("Artikelbezeichnung") Then
                accID = accRec("ID")
                accRec.Edit
                accRec("Artikelbezeichnung") = rng.Offset(0, 1).Value
                accRec.Update
            End If
        End If
    Next rng
    
    'Schließen der Objekte
    accRec.Close
    accDB.Close
    accApp.Quit
    Set accRec = Nothing
    Set accDB = Nothing
    Set accApp = Nothing
    
    xlWB.Close SaveChanges:=True
    xlApp.Quit
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing

End Sub

Ich würde mich sehr über Hilfe freuen!

 

Moin Apollo,
Du kannst die Excel-Tabelle mit Access verknüpfen, dann ist kein händisches Kopieren/Kontrollieren nötig.
Externe Daten/Neue Datenquelle/Aus Datei/Excel

 

Hallo Doming ich würde das aber gerne automatisiert per Button machen lassen. Das jedes Mal händisch machen zu müssen ist ein wenig zeitraubend

 

Das hast Du falsch verstanden, die Exceltabelle ist durch die Verknüpfung eine Quelle für die Datenbank.

 

Ja das habe ich falsch verstanden.
Wie kann man eine solche Verknüpfung machen ?

 

Mir zeigt es dann aber nur den Tabelleninhalt an wenn ich das Excel Blatt beöffnet habe. Außerdem ist es eine relativ große Tabelle und wenn ich durch die Tabelleninhalte in Access druchgehe kommt das System nicht immer ganz mit.
Und wenn ich ausversehen etwas in Excel beispielweise ändere dann wird es ja sofort in Access hochgeladen. Ich würde es lieber "manuell" hochladen wenn ich fertig mit dem bearbeiten der excel tabelle bin

 

Hallo,
was ist der letzte Teil der Frage, die Aktualisierung der Exceltabelle mit den Accessdaten?
Wo stoppt der Code mit der Fehlermeldung?

 

Es hat immer bei xlWs Datensatzvergleich gestoppt. Ich habe den Code jetzt aber mal ein bisschen umgeschrieben. Er läuft auch soweit jedoch sagt er mir abwechselnd immer wieder das er die Mappe "Tabelle1" (so heißt mein Test-Arbeitsblatt in Excel) nicht findet. Oder das der Administrator die Mappe gesperrt hat. Weißt du vielleicht woran das liegen kann?

Code:

Option Compare Database

Sub VergleichUndUpdateDerTabellen()

    On Error GoTo ErrorHandler
    
    ' Öffnen der Access-Datenbank
    Dim db As DAO.Database
    Set db = OpenDatabase("C:\Users\xxx\Documents\Database1.accdb")
    
    ' Öffnen der Excel-Datei
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    Dim xlWorkbook As Excel.Workbook
    Set xlWorkbook = xlApp.Workbooks.Open("C:\Users\xxx\Documents\xxx.xlsx")
    Dim xlWorksheet As Excel.Worksheet
    Set xlWorksheet = xlWorkbook.Worksheets("Tabelle1")
    
    ' Öffnen der Access-Tabelle
    Dim tblAccess As DAO.Recordset
    Set tblAccess = db.OpenRecordset("Name der Tabelle in Access")
    
    ' Abfrage der Excel-Daten
    Dim rstExcel As DAO.Recordset
    Set rstExcel = db.OpenRecordset("SELECT [Primärschlüssel], [Erste Spalte die verglichen werden soll], [zweite Spalte die verglichen werden soll] FROM [Tabelle1]IN 'C:\Users\xxx\Documents\xxx(gleiches xxx) .xlsx' [Excel 12.0 Xml;HDR=YES;IMEX=2]")
    
    ' Schleife durch alle Datensätze in der Excel-Tabelle
    rstExcel.MoveFirst
    Do Until rstExcel.EOF
    
        ' Überprüfen, ob der Datensatz in der Access-Tabelle vorhanden ist
        tblAccess.FindFirst "PrimaryKeyColumnName = '" & rstExcel![Primärschlüssel] & "'"
        If tblAccess.NoMatch Then
            ' Wenn der Datensatz nicht gefunden wurde, fügen Sie ihn hinzu
            tblAccess.AddNew
            tblAccess![Primärschlüssel] = rstExcel![Primärschlüssel]
            tblAccess![Erste Spalte die verglichen werden soll] = rstExcel![Erste Spalte die verglichen werden soll]
            tblAccess![zweite Spalte die verglichen werden soll] = rstExcel![zweite Spalte die verglichen werden soll]
            tblAccess.Update
        Else
            ' Wenn der Datensatz gefunden wurde, aktualisieren Sie ihn
            If tblAccess![Erste Spalte die verglichen werden soll] <> rstExcel![Erste Spalte die verglichen werden soll] Then
                tblAccess.Edit
                tblAccess![Erste Spalte die verglichen werden soll] = rstExcel![Erste Spalte die verglichen werden soll]
                tblAccess.Update
            End If
            If tblAccess![zweite Spalte die verglichen werden soll] <> rstExcel![zweite Spalte die verglichen werden soll] Then
                tblAccess.Edit
                tblAccess![zweite Spalte die verglichen werden soll] = rstExcel![zweite Spalte die verglichen werden soll]
                tblAccess.Update
            End If
        End If
    
        rstExcel.MoveNext
    Loop
    
    ' Schließen aller Objekte
    rstExcel.Close
    Set rstExcel = Nothing
    tblAccess.Close
    Set tblAccess = Nothing
    xlWorkbook.Close SaveChanges:=False
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Set db = Nothing
    
    ' Meldung bei erfolgreichem Durchlauf
    MsgBox "Die Daten wurden erfolgreich verglichen und aktualisiert.", vbInformation
    
    Exit Sub

ErrorHandler:
    MsgBox "Fehler: " & Err.Number & vbCrLf & Err.Description, vbCritical, "Fehler bei der Datenaktualisierung"
    
    Set rstExcel = Nothing
    Set tblAccess = Nothing
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    'xlApp.Quit
    Set xlApp = Nothing
    Set db = Nothing

End Sub

 

Hm, ich kann so gerade keinen Fehler sehen, vielleicht aber jemand, der darin besser bewandert ist.
Was mir auffällt: "PrimaryKeyColumnName = '" & rstExcel![Primärschlüssel] & "'"
Dein Primärschlüssel ist eine String-Variable? ColumnName klingt für mich nach Spaltenüberschrift.

 

Ist doch eigentlich eine Spaltenüberschrift. PrimarKeyColumnName wie auch [Primärschlüssel] wird durch den Primärschlüssel der Access Tabelle ersetzt