Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
    
I think your best shot will be using the web services –  Renzo Apr 19 '11 at 8:10
add comment

3 Answers 3

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?

share|improve this answer
    
(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 '11 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 '13 at 7:54
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
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 '12 at 13:40
    
The UpdateChanges method is not available in any version of Excel later than 2007. –  variant Jun 4 at 19:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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