June 11, 2021
I'm looking for some ideas/suggestions on how to develop functionality that copies part of a certain workbook (excel table) to a new workbook (excel table). The new workbook will be modified independently (only the content of the table can be changed, so no addition of rows, columns etc). The modified table from the seperate workbook has to be merged back into the original workbook. The reason for this is the first workbook ("mother" workbook) is a shared workbook over SharePoint and could be locked for editing at the time the person responsible for making the modifications wants to do so. Therefore the process as just sketched is currently done manually, copying/pasting to new file, and copy/pasting back after modification.
Before deep diving I'm basically looking for tips how to automate this best in Excel (I'm familair with VBA and Power Query).
Any suggestions are highly welcomed.
November 8, 2013
Hard to see the reasons for this process, if it's shared, better to not lock it for editing, and there is no more need to edit in another file, then send back changes, basically you want to ... overwrite what the edit responsible person does. This way anyone using this process become editors. Why not stop locking it?
If you use the sharing tools, one can easily see what cells was changed by more than 1 user, the owner can choose whose changes to keep in case of such conflicts.