How can I develop an Excel plug-in to edit external data in an Excel data table?
Excel can make connections to external data sources but as far as I am can see they are one-direction read-only data tables. What I am trying to do is something like TFS plug-in for excel. I am sure there are many more ones like that.
For those who do not know that plug-in: When installed, TFS Excel plug-in takes place as a new menu in Excel. Through that menu you can open a connection to a TFS server and bring your (work item) records into Excel as an Excel table. You can add new rows or edit the data in the table. Some cells has drop down lists attached to them but only valid options are shown in the list and that is different for each record. You can edit rows in the table and you can bulk push those records back to server.
I don't know if it makes a difference but the connection and update operations on my datasource will be through web services.
I guess this would require some serious development but I am lost between web pages about external data ranges (which are only for reading). Can someone please direct me to some further reading on the topic?
External Data Ranges will not help you so you can stop reading web pages about them. You're correct that they're read only. You could use them for the read part of your operation, but you'll be doing so much coding around the write part, you might as well just control everything. You just won't get enough benefit from External Data Ranges to warrant using them at all for this type of situation. In my opinion, of course.
If you were reading and writing to a database, you would likely use ActiveX Data Objects (ADO). You would read in a recordset, monitor it's changes, then write back to the database using UPDATE, DELETE, and INSERT statements as necessary.
If you'll be interacting with the database through an API, as you seem to indicate, you will probably use Microsoft XML library, specifically the MSXML2.XMLHTTP object. You can use GET, POST, PUT, DELETE, and anything else you can do through HTTP.
If you've never used XMLHTTP before, you'll have a little learning to do. But it's not particularly difficult and there's a ton of info available. The hard part, in my opinion, is tracking the changes made to the Excel sheet. If you allow the user to use Excel's native editing features, it can be difficult to keep track of the changes made. If you go to a total lockdown situation where the user has to, say, use your menu item to delete a record, then you have to ask why you're using Excel (there still may be good reasons, but familiarity with Excel's interface won't be one of them because you'll be replacing it with yours).
Maybe you already have a strategy for this. But if not, search for "detect deleted row with worksheet change event" to get a feel for some of the challenges you'll face. If you have a way forward, then go read up on XMLHTTP and you should be all set.