VBA to update data from Excel table to Sharepoint List

[dhjacob]

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

[#2]

@dhjacob 

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:

https://www.layer2solutions.com/solutions/sharepoint-on-prem-data-integration/excel-data-sharepoint-...

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)

[#3]

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 ListObject

Set 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