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

mir994stan

New Member
Joined
Jul 18, 2021
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2021
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mir994stan

New Member
Joined
Jul 18, 2021
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,483
Messages
5,770,361
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top