Shared Workbooks with VBA

[7PCGamer]

Hi,

i have developed the following architecture to manage and report on the bidding process:

- a workbook that generates quotes in a CPQ format, deployed on the salespersons local machine
- after the bids are finalized the salesperson automatically uploads the quote in a centrally hosted workbook through a VBA procedure.. this workbook is then used by designated reviewers & approver to accept/ reject or modify quotes automatically through VBA.. depending on the status of the quotes the salesperson can then generate a final bid using the local workbook mentioned earlier, again automatically through VBA.. this generates and publicizes a pdf copy of the final bid to a cross-functional team
- a hosted workbook of excepted components which have passed the design stage but are not yet manufactured.. here we store unit costs of components that cannot be directly fetched from the NAV database

the second workbook also contains various controls like bid expiry dates, margin controls, deal size controls which the first workbook looks up while generating the recommended quote. this workbook is currently in exclusive access mode.. due to which the salespeople and reviewers/approvers cannot modify the data at the same time.. since the reviewers/approver have to think over the quote for a while the second workbook can stay open for some time not allowing sales people to make modifications..

as a result i have tried sharing the workbook for concurrent access.. but following issues have cropped up:
- the reporting module on the second workbook requires role based views which have been automated through Protect/Unprotect VBA procedures.. not supported by shared workbooks.. the solution I figured was to unshare the workbook before running the Protect/Unprotect procedure and then resharing the workbook.. is this a good solution.. because when i manually try to unshare/reshare the workbook throws a message stating that all current users will be thrown out and changes at their end will not be saved
- i have developed a way of assigning unique transaction ids for all quotes being submitted into the second workbook by sales people.. the procedure basically looks at the maximum transaction number in the current list and starts numbering the new entry from thereon.. what would be the behavior if 2 sales people upload the quote at the same time.. is there any chance of id replication...loss of uniqueness?

Appreciate your patience for looking at this long post...

Regards.
7PCGamer

 

[dmt32]

Hi welcome to forum,
As you have discovered, sharing workbooks has a number of limitations and most here, would suggest that such an approach is best avoided.

Glancing through your post, it would seem, if I have understood it all correctly, like you have taken the practical solution of providing your users with a local template workbook that uploads its data to a centrally hosted workbook (database) to avoid multi user conflicts which is good.

However, allowing reviewers / approvers to manually open the hosted workbook in read / write mode the above approach as you have discovered fails – not so good.

Perhaps the solution to your problem would be to create template versions of your hosted workbook for your reviewers / approvers where they can read from & write data to your hosted workbook (database) – this way, hosted workbook is only opened by VBA for period of time necessary to write data to it.
Worth noting that if opening the host workbook to read data only you can do this in read only mode which will reduce risk of other user conflicts.

Difficult to comment on your last point as depends who you have written the code sequence in assigning the ID number – If you assign the number at point workbook is saved I would have thought chances of two or more users saving workbook at exactly same moment in time would be minimal so sequences should be maintained.

Hope thoughts helpful & maybe others here can offer further guidance

Dave

 

[7PCGamer]

your understanding is perfect..

as suggested by you, i am already looking at one of the two possible solutions:
1. instead of uploading quotes directly into the hosted workbook.. the local workbook would dump files in a specified format in the same folder.. when the reviewer opens the hosted workbook the VBA proc in it would automatically crawl through the folder and upload data and kill those files
2. the solution you have mentioned

.. both would require some bit of development time for refactoring the code etc. ... i am looking at the following shortcuts in the meanwhile
1. Time sharing.. no uploads possible after 4:00pm and no reviews possible before 4:00pm.. i think i can handle this
2. auto close the hosted workbook after 5 minutes of inactivity... would need some help here.. if someone can point to a solution

also, dont have much experience with shared workbooks.. whats the worst that can happen if i choose the unshare unprotect share procedure... any risk of data loss/corruption...should this be completely avoided?

 

[dmt32]

Hi,
Personally, I would avoid the last option of sharing the workbook as results you get may not be what you expect.

Time slot sharing probably would be a workable solution in short term providing your users look at the clock or you build some additional code in to their workbooks to check the time for them.

Auto closing another possible idea but if opened in read / write mode still does not resolve users want to submit data.

Solution I mentioned can be made to work but only you know the complexity of your application & how much additional work this would be.

Dave