Send data from one macro enabled workbook to another macro enabled workbook on network while its open by another user?

[mir994stan]

Greetings,
I run into problem, when i wanted to test my workbook project... Looking for a solution and suggestion how to make this possible, if its even possible?
I tried with Share Legacy, but it can t share macro enabled workbook or workbook with a table...
On my network i have one workbook (Storage) for my inventory with all items listed in it, and second workbook (Production) is for my college in other department where i need to paste copied data from my workbook.
Main problem here is that when Production workbook is open by another user, it Read-only for me and i can t paste values into it. He needs to close it and then i open again, paste values, save & close, he opens it again...
For that purpose i made macro to sent data to his workbook, but i didn t know that macro enabled workbook can t be shared with MS Excel Shared Legacy...

Is there any solution for this problem? Thanks in advance

 

[mir994stan]

SOLVED: just to say i menage to make this work. I added a 3th workbook with only one sheet, where data is exported from Inventry WB, if Production WB is opened by another user. Also i made IF in macro for WB opening, if Production WB is closed send data directly to it, if not send data to WB3, data is stored always in next empty row in WB3. When user in Production WB open specific sheet, event is trigered and macro open WB3 copy data to Production WB and delete all in WB3, to prevent duplicate values being copied to Production WB. Tomorrow i will try co-authoring feature at work, but this method works just fine.
Guys thanks for the idea. Cheers.

 

[mir994stan]

You have to consider a lot of things designing a system like that. I did it once but eventually changed it to an access database to serve as data exchange and storage.
There are many ways to solve a problem.

I didn t use MS Acces since 2013... I would need to refresh my knlowedge about Access.

 

[bobsan42]

I didn t use MS Acces since 2013... I would need to refresh my knlowedge about Access.

It does not have much in common with Access.
Just making a few tables in the db file.
Then you create a connection to it using for example ADODB or DAO.

 

[rlv01]

Well, we all use Office 365 on computers, but i never used a shared workbook before, i would need to get more knowledge about co-authoring' feature. Maybe i could find some solution.
Its real pain when i need to add data to WB and is used by someone else, and i need to call him to close it for a minute...

With a messaging system like Microsoft Teams, it's a lot easier to ping someone who is hogging the shared workbook. You don't need to 'call' anyone. We use excel for full-on team collaboration with a lot of contributors, and early experimentation with ad-hoc sharing was shall we say, "unsatisfactory". We ended up adopting a subversioning system (Tortoise SVN) which enables a check-in, check-out system along with keeping an activity log of who did what, and when. Audit trails are hugely important for team collaboration, as is the ability to roll back to an earlier version. This last is essential because one of the lessons learned with shared workbooks is that it is a matter of when, not if, one of the users will make a gross error that damages worksheet integrity in a way that cannot be easily fixed. Another alternative is to shift over to something like Smartsheets which was designed from the ground up for team collaboration, but the downside there is that SmartSheets while excel-like, is not excel and does not come close to having the same feature set. We have not yet evaluated the new 365 'co-authoring' feature, but it looks like it has moved Excel a lot closer to SmartSheets in terms of collaboration abilities.