VBA to update data from Excel table to Sharepoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-2538717%22%20slang%3D%22en-US%22%3EVBA%20to%20update%20data%20from%20Excel%20table%20to%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538717%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20VBA%20code%20for%20updating%20data%20from%20Excel%20sheet%20in%20columns%20or%20table%20format%20to%20Sharepoint%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3Edhjacob%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2538717%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2539460%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20update%20data%20from%20Excel%20table%20to%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101453%22%20target%3D%22_blank%22%3E%40dhjacob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ECertainly%20not%20the%20most%20suitable%20person%20to%20help%20you%20in%20your%20project.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EBut%20this%20does%20not%20prevent%20me%20from%20sending%20you%20information%20that%20might%20help%20you%20further.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fblog.fumus.de%2Fsharepoint%2Fsharepoint-listen-in-excel-aktualisieren-und-bearbeiten-3%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EAus%20Excel%20Tabellen%20werden%20SharePoint%20Listen%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIt's%20an%20old%20link%2C%20but%20as%20far%20as%20I've%20read%20the%20comments%20it%20seems%20to%20work.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fexport-an-excel-table-to-sharepoint-974544f9-94bc-4aa8-9159-97282d256dab%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EExport%20an%20Excel%20table%20to%20SharePoint%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EYou%20can%20export%20data%20from%20an%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EExcel%20table%3C%2FA%3E%20to%20a%20SharePoint%20list.%20When%20you%20export%20the%20list%2C%20Excel%20will%20create%20a%20new%20SharePoint%20list%20on%20the%20site.%20You%20can%20then%20work%20with%20the%20data%20on%20the%20site%2C%20just%20like%20you%20would%20for%20any%20other%20SharePoint%20list.%3C%2FP%3E%3CDIV%20class%3D%22ocpAlert%22%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%26nbsp%3BExporting%20a%20table%20as%20a%20list%20does%20not%20create%20a%20data%20connection%20to%20the%20SharePoint%20list.%20If%20you%20were%20to%20update%20the%20table%20in%20Excel%20after%20exporting%20it%2C%20the%20updates%20will%20not%20be%20reflected%20in%20the%20SharePoint%20list.%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EFurther%20information%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHere%20is%20a%20tool%20for%20connecting%20SharePoint%20lists%20with%20external%20data%20sources%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.layer2solutions.com%2Fproducts%2Flayer2-business-data-list-connector%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.layer2solutions.com%2Fproducts%2Flayer2-business-data-list-connector%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThe%20data%20is%20regularly%20updated%20automatically%20via%20a%20timer%20job%20(not%20deleted%20and%20reinserted).%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EAs%20a%20result%2C%20the%20lists%20linked%20in%20this%20way%20can%20also%20be%20used%20as%20a%20lookup%2C%20of%20course%20also%20for%20everything%20else%2C%20such%20as%20forms%2C%20search%2C%20workflows%20...%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThe%20connection%20to%20Excel%20is%20easy%2C%20just%20fill%20in%20a%20form%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.layer2solutions.com%2Fsolutions%2Fsharepoint-on-prem-data-integration%2Fexcel-data-sharepoint-integration%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Ehttps%3A%2F%2Fwww.layer2solutions.com%2Fsolutions%2Fsharepoint-on-prem-data-integration%2Fexcel-data-sharepoint-integration%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EJust%20download%20and%20try%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.layer2solutions.com%2Ffree-trials-of-layer2-products%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.layer2solutions.com%2Ffree-trials-of-layer2-products%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%20class%3D%22ocpAlertSection%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

1 Reply

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