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.
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
UpdateChanges method in the ListObject in my Excel 2010. If fails with an error 1004: "Application-defined or Object-defined error"
Commented
Oct 28, 2014 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
Excel to SharePoint now redirects to a generic page. Thanks, Microsoft. That really helps.
Or you can use Sharepoint list synchronizer that will do that for you in a single click: SharePoint List Synchronizer I think there is a free version that you can try out.
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
You have multiple ways:
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. :)
Public Const SHAREPOINT_SITE = "-----Sharepoiny Site Name-----"
Public Const SHAREPOINT_LIST = "-----Sharpeoint List Name-----"
Public Const SHAREPOINT_VIEW = "{328CC815-1952-46A8-BG8B-EE4456AF13E8}"
These should be declared and call in the array in the ImportSharepoint function as,
Sub ImportSharepointList()
Dim ws As Worksheet
Dim src(2) As Variant
Dim iz As Integer
'--- check if config sheet is available
For iz = 1 To Sheets.Count
If Sheets(iz).Name = LIST_SHEETNAME Then
Application.DisplayAlerts = False
Sheets(iz).Delete
Application.DisplayAlerts = True
Exit For
End If
Next
Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = LIST_SHEETNAME
Set ws = Worksheets(LIST_SHEETNAME)
src(0) = SHAREPOINT_SITE & "/_vti_bin"
src(1) = SHAREPOINT_LIST
src(2) = SHAREPOINT_VIEW
If you correct this. This error won't occur. It will proceed to the further steps.
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 columns which records have same EmployeeCode in List and in Excel.
This will update until record found in excel.
Open in datasheet view , delete the existing columns and paste the new ones. It takes a while for large data but still you can avoid coding . Make sure you match the columns as attachment is the first field and you will have to exclude.
Please use this tool to insert new records & update existing records from excel to any list.
https://o365code.blogspot.com/2021/06/export-import-from-excel-file-in.html
Thanks, Gaurav Goyal
