Sharing Excel Workbook with VBA Macro (Introduction).

Sharing a spreadsheet with number of workers in the office is possible with macro enable excel workbook. I have been using google sheets for a long time but when it comes to working in an office, it just doesn't feel quite the same as using desktop excel workbook.

Were there times, you had to ask your co-worker to close the excel files so you can start using it? You've ended up with read only file and someone forgot to close it.

So here is the concept of sharing a workbook. First we need to create two workbooks, front-end and the back-end. The front-end will be used as your viewing data, and the back-end will be used as saving the data. It's really simple concept but people don't realized that modern computers are so fast that this can be done with excel application.

FrontEnd(Joe)workbook.xlsm (Macro enable)
BackEnd-workbook.xlsx

So, how do we share?

Displaying:
Joe's file will open the back-end workbook and copy it, paste into your Joe's worksheet, and close back-end file. This happens really fast in 1-2 sec and you will not feel that it's slow.

Writing:
We can use the same concept as displaying the date but this time we can use macro to open back-end workbook, write, copy data, paste date, and save & close.

Are you with me so far?
Now Peter as a second user, copy the Joe's workbook, change the file name to:

Frontend(Peter)workbook.xlsm

With a click of a button, the macro will open Back-end workbook, copy it, paste it and close the back-end workbook.

Now we have two people sharing one workbook. This will happen simultaneously as you click buttons. Amazing!!

To be continued...
ExcelOwl .