8

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)?

Improve this question
1
  • I think your best shot will be using the web services Commented Apr 19, 2011 at 8:10

3 Answers 3

Reset to default
4

Please check last section of below page:

http://flylib.com/books/en/3.464.1.67/1/

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
Improve this answer
3
  • 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. Commented Sep 11, 2012 at 13:40
  • The UpdateChanges method is not available in any version of Excel later than 2007. Commented Jun 4, 2014 at 19:53
  • @thornomad It still works for me, you just can't save a workbook with this feature persisting. Commented Nov 21, 2019 at 8:35
2

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

http://translate.google.com.au/translate?hl=en&sl=ja&u=http://207.46.16.248/en-us/library/bb242669(office.12).aspx&ei=sSqtTZ31OIz4swPLxoTRCQ&sa=X&oi=translate&ct=result&resnum=10&ved=0CGUQ7gEwCQ&prev=/search%3Fq%3DListObject.UpdateChanges%2B1004%26hl%3Den%26sa%3DG%26rls%3Dcom.microsoft:en-au%26prmd%3Divns

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

http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx

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

http://www.mrexcel.com/forum/showthread.php?t=284983

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

Improve this answer
2
  • (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?) Commented 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 Commented Mar 19, 2013 at 7:54
2

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.

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.