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 have a SharePoint that has list and I need to be able to update this list with new values from an Excel sheet (using VBA).

This can be done from Access not a problem however I need it to be Excel only.

Is what I am asking possible and if some could point me in the direction of a solution.

share|improve this question

7 Answers 7

up vote 2 down vote accepted

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
    
There's no UpdateChanges method in the ListObject in my Excel 2010. If fails with an error 1004: "Application-defined or Object-defined error" –  Henrique Abreu Oct 28 at 10:34

You can synchronize Excel 2007/2010 and SharePoint 2007/2010 with a download and install of a Microsoft Excel Addin.

Here are some articles that has detailed description on how this is done - Excel to SharePoint and SharePoint to Excel

share|improve this answer
    
This is one the that worked however few minute after finding solution it wasn't needed due to SQL server that no one knew about can be used. –  Sleepy Rhino Apr 23 '12 at 10:53
    
Link Excel to SharePoint now redirects to a generic page. Thanks, Microsoft. That really helps. –  teylyn Jan 10 at 9:32
    
I believe the Excel to SharePoint link originally pointed to what is now this page: msdn.microsoft.com/en-us/library/bb462636%28v=office.11%29.aspx In case that link goes down, perhaps it might be Googlable via "Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists" –  variant Jun 4 at 19:28
    
Thanks @variant. Edited the answer. –  Deepu Nair Jun 5 at 8:33

Or you can use Sharepoint list synchronizer that will do that for you in a single click: http://www.softfluent.com/products/sharepoint-list-synchronizer I think there is a free version that you can try out.

share|improve this answer

It is only possible to create a new list based on an Excel-Sheet, but you cannot update an existing list with Excel.

To save an Excel sheet to a list, click Site Actions->More options->Import Spreadsheet Import Spreadsheet

share|improve this answer
    
I am aware of this option, creating the list is not the problem it is having the ability to update/change it –  Sleepy Rhino Apr 20 '12 at 10:54

You have multiple ways:

  1. Excel Services - direclty on site - requires Enterprise license
  2. Excel PowerPivot (FREE AddOn) supports REST which would enable connection to data and edit data directly see here http://www.microsoft.com/en-us/bi/powerpivot.aspx
  3. Business Connectivity Services - connect to a lot of types of data (SQL Databases, Excel & CSV files, etc.) and provide real-time data access
share|improve this answer

This has to be done by code if update/add the list items using the excel. I use the custom workflow action to update the list items from excel file, the excel file which store the list info and all items. So first upload the excel file to the document library then run the workflow to update/Add the item in excel to list. The workflow action will read the data from the excel then copy to the list items. You could write Convert class to convert the data to corresponding SharePoint field data. Then make the copy. The happiest thing that it could also run in sandbox solution. :)

share|improve this answer

I have done same thing using Excel Addin 2013. You can do the same using Addin by writing code of CSOM for SharePoint List. You can use this Event in Addin :-

void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)

And In this event you can add code like following of mine :-

try
        {
            Microsoft.SharePoint.Client.ClientContext clientContextproj = new Microsoft.SharePoint.Client.ClientContext("sharepointURL");
            Web siteproj = clientContextproj.Web;
            clientContextproj.AuthenticationMode = ClientAuthenticationMode.Default;
            clientContextproj.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
            CamlQuery query1 = new CamlQuery();
            query1.ViewXml = "<View><Query><FieldRef Name='ID'/><Value Type='Text'></Value></Query></View>";

            List resourceMaster = siteproj.Lists.GetByTitle("Listname");
            Microsoft.SharePoint.Client.ListItemCollection collListResourceMaster = resourceMaster.GetItems(query1);
            clientContextproj.Load(collListResourceMaster);
            clientContextproj.ExecuteQuery();

            Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
            bool flag = true;
            int counter = 2;
            while (flag)
            {
                string columnHeader = "A" + counter.ToString();
                Excel.Range row = activeWorksheet.get_Range(columnHeader);
                if (row.Value2 != null)
                {
                    if (collListResourceMaster.Count != 0)
                    {
                        foreach (Microsoft.SharePoint.Client.ListItem targetListItemProj in collListResourceMaster)
                        {
                            var currentResourceId = targetListItemProj["EmployeeCode"];
                            if (currentResourceId != null && currentResourceId.Equals(Convert.ToString(row.Value2)))
                            {
                                Excel.Range Exprow = activeWorksheet.get_Range("C" + counter.ToString());
                                string ValueTotal = Convert.ToString(Exprow.Value2);
                                string[] beforeExp = ValueTotal.Split('.');
                                int totalMonth = Convert.ToInt32(beforeExp[0]) * 12;
                                if (beforeExp.Count() > 1)
                                {
                                    totalMonth += Convert.ToInt32(beforeExp[1]);
                                }

                                targetListItemProj["FieldToUpdate"] = totalMonth;
                                targetListItemProj.Update();
                                clientContextproj.ExecuteQuery();
                                break;
                            }
                        }
                        counter += 1;
                    }
                }
                else
                {
                    flag = false;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

This code updates column which record have same EmployeeCode in List and in Excel. This will updates until record found in excel. Let me know If you have any problem. email: hayatsama1@gmail.com

share|improve this answer

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.