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:
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:
- Make two workbooks:
- 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)
- Database: saved on OneDrive
- When someone opens up Main Workbook, Database opens up in the background, allowing Main Workbook to read and write to it.
- Any changes to Main Workbook would get pushed to Database via VBA.
- 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.
