VBA matching cell range values to table column name-CodePudding

I have a sheet with columns and a table from database. I want to match each values of the column to the TABLE column. If a column value does not exist in the TABLE column then the system ask if you want to ADD that column value. So, just some kind of looping or mapping each of values. Thank you so much for your help.

What I've tried:

      Set adocn = New ADODB.Connection
       adocn.Open sConnString
       Set recset = adocn.Execute("Select * from table1")
       
       Range("A1").Select
       Set a = Range(Selection, Selection.End(xlToRight))
       
       For Each col In a
         For Each fld In recset.Fields
          If fld.Name <> col.Value Then
       
       If MsgBox("Add " & col.Value & " to column?", vbYesNo   vbQuestion) = vbYes Then
       aa = "insert into table1 values('" & col.Value & "')"
       adocn.Execute aa
       End If
       
       Exit For
       Exit For
       
        Next
       Next

CodePudding user response：

Sub AddMissingValues()
    ' Declare variables
    Dim adocn As ADODB.Connection
    Dim recset As ADODB.Recordset
    Dim col As Range
    Dim fld As Field
    
    ' Establish database connection
    Set adocn = New ADODB.Connection
    adocn.Open sConnString
    
    ' Retrieve data from table1
    Set recset = adocn.Execute("Select * from table1")
    
    ' Select the first cell in column A
    Range("A1").Select
    
    ' Set the range to be the entire column
    Set a = Range(Selection, Selection.End(xlToRight))
    
    ' Loop through each cell in the column
    For Each col In a
        ' Flag to check if value already exists in the table
        Dim exists As Boolean
        exists = False
        
        ' Loop through each field in the recordset
        For Each fld In recset.Fields
            ' Check if the field name matches the cell value
            If fld.Name = col.Value Then
                exists = True
                Exit For
            End If
        Next
        
        ' If the value doesn't exist in the table, prompt user to add it
        If exists = False Then
            If MsgBox("Add " & col.Value & " to column?", vbYesNo   vbQuestion) = vbYes Then
                adocn.Execute "insert into table1 values('" & col.Value & "')"
            End If
        End If
    Next
    
    ' Close database connection
    adocn.Close
End Sub