regrouped tables workbook (sharepoint) from multiple books (local)

Occasional Contributor

Hello, 

 

We are in a mixed environment of 2013/2016 and 365 (I have no influance on software versions)

 

  • Locally (network share): we have 15 client folders, each containing a clientName.xlsm (the structure of each client book is identicle)
  • Each clientName.xlsm has a tab ("spec")
  • Each spec tab has a table ("clientName_Table") (all client tables have the same structure)

I want to create a workbook hosted on our Teams sharepoint that has 15 tabs (one for each client). On each client tab would be the table from the associated (local) client book. 

 

This sharepoint hosted workbook would be accessed by multiple people at the same time. 

 

The objective is that when a modification or a new line is added to a local book table the changes/additions would be "synchronised" to the sharepoint book. 

 

The inconsisitant nature of the 2013/2016 installs means not everyone has powerquery, VBA seems the best option. 

 

My current best solution involves having a onedrive sync of the sharepoint hosted book on my local machine, with VBA in each client book that copies the tables databody range to the sharepoint books corresponding client table. It works, however this seems to (inconsistantly) cause sysnc problems (one drive creating two copies of the file) when the sharepoint book is open in 365. 

 

Can anyone propose a better solution that I can investigate?

 

Thanks for taking the time to read this.

 

 

1 Reply
however this seems to (inconsistantly) cause sysnc problems

if you want to sysnc on line data basis
rather than file basis,I am afraid excel not convenient to accomplish this task。