Public Const vURL As String = "https://mysharepointsite.com/sites/"  '<-- This is from Settings URL
Public Const vGUID As String = "{1d42d-367-4d9L-2tra-b18ikeuwi10}"   '<--RegistrationImport SPList
Public vConn As Object 'Needs ActiveX Data Objects 2.8
Public vConnString As String 'Needs ActiveX Data Objects 2.8
Public vRS As Recordset 'Needs ActiveX Data Objects 2.8
Public vSQL As String
Public vCmd As ADODB.Command

Public vWB As Workbook
Public vWS As Worksheet
Public tbl As ListObject
Public i As Integer

Sub SPlist()
Set vWB = ActiveWorkbook
Set vWS = vWB.Sheets("RenewalForm")
Set tbl = vWS.ListObjects("Renewal")


vConnString = "Provider=Microsoft.Ace.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & vURL &     ";LIST=" & vGUID & ";" 'VIEW=" & spVIEW & ";"

' Create and open the connection
Set vConn = New ADODB.Connection
vConn.ConnectionString = vConnString
vConn.Open

' Loop through each row in the table, skipping the header row
For i = 1 To tbl.ListRows.Count
    ' Get the values for each column
    With tbl.ListRows(i).Range
        vSQL = "INSERT INTO [RegistrationIMPORT] (" & _
                "[Request Type], " & _
                "[Requester], " & _
                "[Unit], " & _
                "[Full VIN], " & _
                "[Plate Number], " & _
                "[Vehicle needs sticker], " & _
                "[Vehicle needs plate], " & _
                "[Comments]) " & _
                "VALUES (" & _
                "'" & .Cells(1, tbl.ListColumns("RequestType").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Requester").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Unit").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Full VIN").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Plate Number").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Vehicle needs sticker").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Vehicle needs plate").Index).Value & "', " & _
                "'" & .Cells(1, tbl.ListColumns("Comments").Index).Value & "')"
        
        ' Execute the SQL command
        On Error GoTo ErrorHandler
        vConn.Execute vSQL
    End With
Next i

' Save the active workbook
vWB.Save

' Close and clean up the connection
If vConn.State = adStateOpen Then
    MsgBox "Thank you for submitting the Request!  
vConn.Close
End If
Set vConn = Nothing

Exit Sub

ErrorHandler:
MsgBox "Request was not submitted.  Please make sure all fields have data and resubmit." &    Err.Description
Debug.Print Err.Number, Err.Description
If vConn.State = adStateOpen Then vConn.Close
Set vConn = Nothing
End Sub

The above code works but is pulling in blank Request Type values. I have validation on the Request Type field in the Excel table “Renewal”. I have removed validation from the SharePoint List as this field is now just a text field. I need to keep validation in excel table, but I’m fine with SharePoint field just text or whatever it needs to be to accepts this data. Any ideas on what I need to change in this part of the code to get the value from the Excel table into the SharePoint List?

“VALUES (” & _
“‘” & .Cells(1, tbl.ListColumns(“RequestType”).Index).Value & “‘, ” & _