CODEWITHSUNDEEP
excelcollaborationvba
HappyVba
HappyVba

Reputation: 101

Sharing xlsm file

I have an xlsm file located in my work place internal server. Say we are 2 workers who use this file, which includes 2 sheets, My_sheet and His_sheet.

I’m trying to find way for each one of us to work on “his” sheet, means I can edit and save only My_sheet when my co-worker edits His_sheet.

I managed to add a little input box at the opening of the file (Workbook) which, by the different passwords we put there, results by protecting the right sheet and unprotecting the other and this works OK.

My problem is actually the “sharing”:

Is there a way for us to work on this file at the same time (simultaneously) and save our changes?

I tried the Excel Share option but it blocks everything. The other option I found is using Google’s cloud but it is impossible for us to use it; we have to leave the file “inside the company”… besides, I understand it doesn’t support scripts so this isn’t an option.

Appreciate every help!

Upvotes: 2

Views: 2691

Answers (1)

freginold
freginold

Reputation: 3956

As @Sorceri said, if your file has to stay on-prem then any cloud options (like co-authoring) are out the window.

There's really no true "sharing" that you could do (that I know of) but here are a couple of hacky workarounds.

Split the file

If each of you only work on your own sheet in the Excel file, is there a reason you can't separate them into two distinct workbooks? That would be the easiest solution. Then you could each open and edit them at any time.

You said it's an .xlsm file, so if you have any macros saved in the file, you'd have to be sure both new files had the macros they needed.

Script your own solution

Since it's only two of you editing the files, and since you each only edit your own worksheet, you could whip up a script to allow you each to work on your individual worksheets without impacting the other's sheet.

There are numerous ways you could do this. One way would be to have the script create a working copy of the file for each of you at the beginning of the work day. Then, at some predetermined off-time (at night, for example) schedule a task to copy the working copy of your sheet and the working copy of your coworker's sheet back into the main file.

Unless someone else needs access to the workbook during the day, this solution should solve your problem (in a roundabout way).

Upvotes: 2

Related Questions