2-way workbook updating

[Lade]

Hello,

I've looked for a bit and haven't found anything that works for what I'm trying to do.
I have a worksheet in one workbook that one person enters information in, and I have another workbook that I want the first one to update to, and vise versa. The solution I thought would work for that would be a VBA Worksheet_Change, but so far that only works if both sheets are in the same workbook. But both sheets are in different workbooks on different computers on a network. Is it possible for the VBA to call out a certain workbook through a file path to update it?

Thanks

 

[Jeffrey Mahoney]

With both workbooks open, you can create a formula to get information the other workbook.

* This is not going to create new lines of data
* You won't be able to update a single table on both workbooks
* Only saved data can be viewed unless you have both open at the same time

VBA can update values from another workbook, but ONLY saved data. The user on the other end would have to save often. There are ways to share workbooks so you can see live changes. I personally don't like them.

 

[Lade]

With both workbooks open, you can create a formula to get information the other workbook.

* This is not going to create new lines of data
* You won't be able to update a single table on both workbooks
* Only saved data can be viewed unless you have both open at the same time

VBA can update values from another workbook, but ONLY saved data. The user on the other end would have to save often. There are ways to share workbooks so you can see live changes. I personally don't like them.

What formula would you recommend? How would I call the file path?

 

[Jeffrey Mahoney]

When you have both WBs open, just start a formula (even a simple formula that returns the value from a single cell). Press = and navigate to the other WB, choose a cell and press enter. When you save the WB, it remembers the path. It won't show the path when the other WB is open, it will show the path when the other WB is closed.

 

[dmt32]

Hello,
both sheets are in different workbooks on different computers on a network. Is it possible for the VBA to call out a certain workbook through a file path to update it?

In the early days of VBA one way we overcome data sharing between workbooks on corporate network was, when it was not possible to connect them to our corporate database, to create a common workbook (database) with single table placed on the server.

User Workbooks were coded to open the database (readonly) to populate their workbook with common data then closed.

any changes posted back to the database coding was created to resolve read/write conflicts (two people having database open at the same time to write data) Whilst approach won’t display real time updates & user(s) may need to refresh their data before posting changes, approach did resolve requirement to share common data between workbooks.

if later versions of excel 365 with co-authoring - Collaborate on Excel workbooks at the same time with co-authoring
do not meet your need, then perhaps this approach worth considering?



Dave

 

[Lade]

When you have both WBs open, just start a formula (even a simple formula that returns the value from a single cell). Press = and navigate to the other WB, choose a cell and press enter. When you save the WB, it remembers the path. It won't show the path when the other WB is open, it will show the path when the other WB is closed.

Seems like the thing with linking the cells together is that it's only one way editing. Is there another way to do it? I was wanting to be able to edit both workbooks from either workbook. And that would also include inserting and deleting rows unfortunately.

 

[Jeffrey Mahoney]

The need you're requiring would be a lot of VBA code. I have created two separate Excel Applications using a Read Only Form that any number of people can open, and an external workbook that saves all the data. The last one I have yet to finish has taken me over a month of coding so far.