Editing Sharepoint 2003 Lists using Microsoft Excel

Sharepoint may be a useful collaboration portal for people involved in a project to work together, but certain functions are still quite clunky. Not sure if it wasn’t setup properly by the company’s IT Infrstructure team or if its just MS software being wonky, but the “Edit in Datasheet” button that is available for all Sharepoint lists does not work properly. Whenever it is clicked, there is an error stating that something is wrong about ActiveX or that a compatible application is not installed.

SharePoint error
SharePoint error

What to do? It can be quite a pain to manage lists through the UI especially if you have to consolidate information and copy-and-paste information between spreadsheets. Typing in each new row one-by-one is an option only for those with great patience and a huge amount of time. Thankfully, I managed to work out one way…

First of all, export the list to a spreadsheet. You will be given a file with the extension .iqy. Open this file using Microsoft Excel and your list’s data will appear. You can make any changes you like to this list, but instead of clicking on Save, you right-click on the sheet, go to Lists/Synchronise Lists. This will automagically update the list on Sharepoint!

Synchronising list with SharePoint using Excel
Synchronising list with SharePoint using Excel

Leave a Reply

Your email address will not be published. Required fields are marked *