Forum Discussion
dhjacob
Jul 12, 2021Copper Contributor
VBA to update data from Excel table to Sharepoint List
Hello Everyone,
I am looking for a VBA code for updating data from Excel sheet in columns or table format to Sharepoint list.
Thanks,
dhjacob
- NikolinoDEGold Contributor
Certainly not the most suitable person to help you in your project.
But this does not prevent me from sending you information that might help you further.
Aus Excel Tabellen werden SharePoint Listen
It's an old link, but as far as I've read the comments it seems to work.
Export an Excel table to SharePoint
You can export data from an Excel table to a SharePoint list. When you export the list, Excel will create a new SharePoint list on the site. You can then work with the data on the site, just like you would for any other SharePoint list.
Note: Exporting a table as a list does not create a data connection to the SharePoint list. If you were to update the table in Excel after exporting it, the updates will not be reflected in the SharePoint list.
Further information:
Here is a tool for connecting SharePoint lists with external data sources: https://www.layer2solutions.com/products/layer2-business-data-list-connector
The data is regularly updated automatically via a timer job (not deleted and reinserted).
As a result, the lists linked in this way can also be used as a lookup, of course also for everything else, such as forms, search, workflows ...
The connection to Excel is easy, just fill in a form:
Just download and try: https://www.layer2solutions.com/free-trials-of-layer2-products
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
- SM_2022Copper Contributor
Hi NikolinoDE
On previous versions I had a code that let me update a SharePoint list that was already exported in Excel to SharePoint again, this means that if I click on the Update button related to the code, it should read the current data on SharePoint and if I entered new values on Excel, they were loaded to SharePoint too. It was a 2-way refresh. Here's the code:
____________________________________________________________________________________________
Sub UpdateSPList()
Dim ws As Worksheet
Dim objListObj As ListObjectSet ws = ActiveWorkbook.Worksheets(1)
Set objListObj = ws.ListObjects("Table1")objListObj.UpdateChanges xlListConflictDialog
End Sub
____________________________________________________________________________________________
Unfortunately, looks like the UpdateChanges method is not available anymore or it doesn't work in Office 365 version. Is there a way to make this work again on Office 365?
Thanks in advance and best regards,
Sinuhé Mariscal