Excel 2010 vba add item to sharepoint list

I found a VBA snippet which update sharepoint list via Listobject's publish method. I hope it can help you.

Public Sub PublishList()' Get the collection of lists for the active sheetDim L As ListObjects
Set L = ActiveSheet.ListObjects
' Add a new listDim NewList As ListObject
Set NewList = L.Add(xlSrcRange, Range("A1:G8"), , True)
NewList.Name = "PartsList"' Publish it to a SharePoint site
NewList.Publish Array("http://sharepointportal.xxx.com/personal/xxx/_layouts/viewlsts.aspx?BaseType=0", _
"NewLists "), TrueEnd Sub

I tried what you said, and even went to creating a column heading the same as what is in the Sharepoint list already (I used exactly the same column names) . I get the following error though:
"Run-Time Error '-21474467259 (80004005)': An unexpected error has occured. Changes to your data cannot be saved."

Public Sub PublishList()' Get the collection of lists for the active sheetDim L As ListObjects
Sheets("ReportRefresh").Select
ActiveSheet.Cells.Clear
Set L = ActiveSheet.ListObjects
' Add a new listDim NewList As ListObject
Set NewList = L.Add(xlSrcRange, Range("A1:K2"), , True)
Cells(1, "A").Value = "Title"
Cells(1, "B").Value = "FileVersion"
Cells(1, "C").Value = "CustomerName"
Cells(1, "D").Value = "RegionName"
Cells(1, "E").Value = "InWarranty"
Cells(1, "F").Value = "ProductType"
Cells(1, "G").Value = "SubDivisionName"
Cells(1, "H").Value = "SiteName"
Cells(1, "I").Value = "Entitlement"
Cells(1, "J").Value = "reportingPeriod"
Cells(1, "K").Value = "RunTime"
Cells(2, "A").Value = "test"
Cells(2, "B").Value = Sheets("Instructions").Cells(3, "b").Value
Cells(2, "C").Value = Sheets("Instructions").cmboCustName.Text
Cells(2, "D").Value = Trim(Sheets("Instructions").cmboRegion.Text)
Cells(2, "E").Value = Sheets("Instructions").cmboSupportWarranty.Text
Cells(2, "F").Value = Trim(Sheets("Instructions").cmboProdType.Text)
Cells(2, "G").Value = Trim(Sheets("Instructions").cmboSubDivision.Text)
Cells(2, "H").Value = Trim(Sheets("Instructions").cmboSite.Text)
Cells(2, "I").Value = Trim(Sheets("Instructions").cmboEntitlement.Text)
Cells(2, "J").Value = Trim(Sheets("Instructions").cmboReportingPeriod.Text)
Cells(2, "K").Value = "10"
NewList.Name = "Report Refresh"' Publish it to a SharePoint site
NewList.Publish Array("http://intranet/dept/glblsptsvc/Reports/_layouts/viewlsts.aspx?BaseType=0", _
"NewLists "), TrueEnd Sub

the sharepoint list already exists so all I want to do is amend a record to the end of the list.

---

Sub UpdateSpecificCells()

'If nobody has the file checked out
If Workbooks.CanCheckOut("http://excel-pc:43231/Shared Documents/ExcelList.xlsb") = True Then
Application.DisplayAlerts = False

'Open the file on the SharePoint server
Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever


ActiveSheet.Cells(2, 7).Value = 100
ActiveSheet.Cells(3, 7).Value = 200
ActiveSheet.Cells(4, 7).Value = 300


'Close the workbook
Workbooks("ExcelList.xlsb").Save
Workbooks("ExcelList.xlsb").Close

End If
End Sub