I'm in the enviable position of having to support a client who wants to maintain and manipulate a complicated business spreadsheet in Excel, and then push a selection of that data to a list in SharePoint for consumption by the rest of the business.

I can successfully publish the data to SharePoint using a VBA macro if the list does not exist, using the ListObject class:

ActiveWorkbook.Worksheets(tgtSheetname).ListObjects(1).Publish Array(site, sname), True

However I have not been able to update the data in the list if it already exists. My client's original code attempted to do this using the following command:

ActiveWorkbook.Worksheets(tgtSheetname).ListObjects(1).UpdateChanges (xlListConflictDialog)

but this throws a Run-time error '1004': Application-defined or object-defined error.

Is it possible to update the data in an existing SharePoint list using VBA (keeping in mind that the list was originally created from this spreadsheet)?

  • I think your best shot will be using the web services
    – Renzo
    Apr 19, 2011 at 8:10

3 Answers 3


Please check last section of below page:


To import list from SharePoint:

Sub ImportListFromSP()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets(2)
    Dim src(1) As Variant
    src(0) = "http://SharePointSiteAddress/_vti_bin"
    src(1) = "89F90972-FD90-4B04-BCEB-81840A82DA5E"

    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

End Sub

To update modifications in SharePoint list:

Sub UpdateSPList()

   Dim ws As Worksheet
   Dim objListObj As ListObject

   Set ws = ActiveWorkbook.Worksheets(2)
   Set objListObj = ws.ListObjects("Table1")

   objListObj.UpdateChanges xlListConflictDialog

End Sub
  • This method of syncing works for me; I have Excel 2007 at work which causes issues when I try to save all this but I am still trying to work around it.
    – thornomad
    Sep 11, 2012 at 13:40
  • The UpdateChanges method is not available in any version of Excel later than 2007.
    – variant
    Jun 4, 2014 at 19:53
  • @thornomad It still works for me, you just can't save a workbook with this feature persisting.
    – Sancarn
    Nov 21, 2019 at 8:35

I have tested this and get the same error. It sort of looks those particular functions are being phased out.


Object Model Changes Since Microsoft Office 2003
Methods Status Status
UpdateChanges UpdateChanges Hidden


Summary: In Microsoft Office Excel 2007, the ability to synchronize the data between a table and a list in Microsoft Windows SharePoint Services is deprecated. This article describes an add-in that enables you to update the information in a SharePoint list from Excel 2007. Unprotecting the workbox had no effect

Unprotecting the workbook has no effect


Can you just save the excel sheet to a document library and use excel services?

  • (Belated) thanks for your answer - I don't suppose you have a good link to information about using excel services to process a sheet after it's been uploaded to a document library (as per your last suggestion?)
    – Dexter
    Jun 3, 2011 at 15:39
  • Update Changes method is eliminated in Excel 2007 and 2010.Excel is unable to update existing list of sharepoint
    – user15700
    Mar 19, 2013 at 7:54

Microsoft now suggests that you use Access to manipulate SharePoint Lists, but it has issues when you use multiple formats in the same list. It works fine if don't have memo fields, I have found.

  • A link to where you read this and a direct quote would greatly improve this answer.
    – TylerH
    Apr 1, 2020 at 14:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.