Creating a Detached "Database" which Multiple Users Can Simultaneously Read/Write To

djsereno

New Member
Joined
Aug 17, 2017
Messages
5
I have a workbook that uses macros and VBA to accomplish a variety of tasks. I'm trying to find a way to implement some sort of co-authoring so that multiple users can be in it at once. However, the macros within the workbook essentially prevent me from using the share/co-authoring features of OneDrive, so I am trying to create some sort of workaround to achieve the same effect.

The main purpose of the workbook is to monitor and edit job fees and budgets as well as employee schedules and hours. What I'm thinking of is removing this data from the main workbook and saving it in some sort of database file. The database file could then be saved/shared via OneDrive. The original workbook is now essentially a "viewer" of the database file, and any changes made to it would be pushed to the database. Similarly, if someone else makes an edit that effects the database, the database would update and then push those changes out to other users.

Does this make any sense? And does anyone have any good ideas on how to implement this sort of functionality? My current thought is to do something like this, but I'm wondering if there are any better ideas:
  1. Make two workbooks:
    1. Main Workbook: macro-enabled, saved locally, could be opened by multiple people as read-only (doesn't really matter because the real data is in Database)
    2. Database: saved on OneDrive
  2. When someone opens up Main Workbook, Database opens up in the background, allowing Main Workbook to read and write to it.
  3. Any changes to Main Workbook would get pushed to Database via VBA.
  4. Haven't quite figured out the best way to do this, but if any changes get made to Database by another user, I need to have these changes push back to Main Workbook. Maybe refresh on cell selection or calculation, although I could see this being very slow/cumbersome.
Thoughts?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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