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

 

[bobsan42]

Can't you reverse the idea so he gets information rather than receives it?

 

[rlv01]

I doubt you will be able to accomplish what you want unless you and your other users upgrade to a version of Excel that supports the new 'co-authoring' feature.

• Excel for Microsoft 365*
• Excel for Microsoft 365 for Mac*
• Excel for the web
• Excel for Android
• Excel for iOS
• Excel Mobile

My day job is in this kind of highly collaborative environment and while having multiple people working on an open worksheet at the same time is ok for trivial applications like todo lists or information logging, to do so for anything mission critical or where the output (engineering calculations to determine the LEL for flammable gas concentrations in a boiler exhaust stack ,say) has a safety impact, is madness.

 

[Gokhan Aycan]

Depending on how you use the Production workbook and how large it is, maybe this would work.

No one keeps the Production workbook open.
Your workbook (when necessary through a macro) opens and syncs (copy-paste-delete stuff) what is relevant to you and close Production.
Your colleague has his own workbook (not Production) does the same.
In case you guys try to sync at the same time, a msgbox opens and informs you of this, trying to open every x seconds for n times.

As I said this all depends on how large the workbook is (if it takes a second to open then should be fine) and how the data on it is used.
Just a workaround kind of thing I had to do once.

 

[mir994stan]

I doubt you will be able to accomplish what you want unless you and your other users upgrade to a version of Excel that supports the new 'co-authoring' feature.

• Excel for Microsoft 365*
• Excel for Microsoft 365 for Mac*
• Excel for the web
• Excel for Android
• Excel for iOS
• Excel Mobile

My day job is in this kind of highly collaborative environment and while having multiple people working on an open worksheet at the same time is ok for trivial applications like todo lists or information logging, to do so for anything mission critical or where the output (engineering calculations to determine the LEL for flammable gas concentrations in a boiler exhaust stack ,say) has a safety impact, is madness.

Hi thanks for reply, well we all use Excel 365, but i don t have expierence with file sharing. I need to investigate that co-authoring featrue

Can't you reverse the idea so he gets information rather than receives it?

Nice idea, but i tried with Data connection via table and set update time to 1 minute, but excel legacy sharing don t support sharing WB with tables... Or to make macro to run on worksheet activation to get data from other workbook hmm? But i didn t tried that yet, i work from Monday. So i will give it a try

 

[mir994stan]

I doubt you will be able to accomplish what you want unless you and your other users upgrade to a version of Excel that supports the new 'co-authoring' feature.

• Excel for Microsoft 365*
• Excel for Microsoft 365 for Mac*
• Excel for the web
• Excel for Android
• Excel for iOS
• Excel Mobile

My day job is in this kind of highly collaborative environment and while having multiple people working on an open worksheet at the same time is ok for trivial applications like todo lists or information logging, to do so for anything mission critical or where the output (engineering calculations to determine the LEL for flammable gas concentrations in a boiler exhaust stack ,say) has a safety impact, is madness.

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...

 

[mir994stan]

Depending on how you use the Production workbook and how large it is, maybe this would work.

No one keeps the Production workbook open.
Your workbook (when necessary through a macro) opens and syncs (copy-paste-delete stuff) what is relevant to you and close Production.
Your colleague has his own workbook (not Production) does the same.
In case you guys try to sync at the same time, a msgbox opens and informs you of this, trying to open every x seconds for n times.

As I said this all depends on how large the workbook is (if it takes a second to open then should be fine) and how the data on it is used.
Just a workaround kind of thing I had to do once.

Well Workbooks are about 1000 rows, every week we clean them, rows with 0 value in colum E are exported to new WB. But wait a minute, do we need like some workbook to act like a excange? WB1(Storage)< <macro open,paste,save,close>> WB3(excange)<macro only import data from WB3, and delete after import>WB2(Production). Could something like that work? That could avoid need of sharing WB on network?

 

[Gokhan Aycan]

I made it so 4 people would work on the same workbook (they all had their own client workbooks, so to speak). It would take a second or so to open-sync-close the main workbook.

As I said, it really depends on certain factors, may work or not. 1000 rows seem reasonable. If you can't find any other way, maybe think about it.

 

[mir994stan]

I made it so 4 people would work on the same workbook (they all had their own client workbooks, so to speak). It would take a second or so to open-sync-close the main workbook.

As I said, it really depends on certain factors, may work or not. 1000 rows seem reasonable. If you can't find any other way, maybe think about it.

Well i will give it a try. Production workbook will recieve between 1 and 50 rows of data with every import. So its not that much, plus there are no calculations. Microsoft engenieers rly needs to solve this problem better ?

 

[bobsan42]

Well i will give it a try. Production workbook will recieve between 1 and 50 rows of data with every import. So its not that much, plus there are no calculations. Microsoft engenieers rly needs to solve this problem better ?

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.