Forum Discussion
Sharepoint Workbook reversion to earlier version due to Internet link broken VBA
A group of eight geographically separated people coauthor an Excel macro-enabled Workbook located on a Sharepoint Server. This system works well with editing by each of the group being autosaved to the Sharepoint workbook in realtime.The group are connected to the Sharepoint through VPN and all use the latest version of Microsoft 365 (Desktop Excel). When a VPN times out or when one of the group's laptop suffers an internet disconnect, the latest version of the Workbook can revert to an earlier version consistent with the version at VPN timeout or internet disconnect of that particular laptop when it comes back on line with Excel and the Workbook open I gather because the autosave refreshes the more modern online Workbook with that of the now aged version on the laptop in question even though other members of the group have not suffered any disconnect and continue to edit the Workbook. Is there a way within VBA to monitor for such a disconnect forcing the laptop in question to close its occurrence of Excel immediately before autosave does its damage when the internet re-connects?
waygar You could use the trick I implemented in the attached file. Note that there is code in both Module1 and in ThisWorkbook. What I don't like about the solution is that the mouse cursor flashes every time the code runs. You may want to make it run less frequent by adapting the Timevalue argument.
3 Replies
Unfortunately, directly monitoring and forcibly closing Excel due to disconnections in a co-authored workbook isn't feasible using VBA solely because of security restrictions and limitations in controlling external processes. However, there are alternative approaches you can consider to mitigate the reversion issue:
1. Leverage Sharepoint Versioning:
- Sharepoint automatically versions uploaded documents. Configure version history to keep a sufficient number of versions (e.g., past hour, daily).
- If a reversion occurs, manually restore the latest version.
2. Implement a "Check-In/Check-Out" System:
- Designate one person as the responsible editor at a time.
- Use a manual system (e.g., shared document) to track who's editing.
- The current editor checks out the workbook, preventing others from modifying it simultaneously.
- Upon completion, the editor checks in the workbook, making it available for others.
3. Explore Third-Party Solutions:
- Consider tools like Docupace or CoGuard that offer greater control over co-authoring, access, and conflict resolution in Excel workbooks stored on Sharepoint.
4. Improve Network Connectivity:
- Investigate potential network issues causing disconnections (e.g., VPN stability, individual internet providers).
- Implement better connection solutions to minimize disruptions.
5. Educate Users:
- Train users to save the workbook manually before VPN timeouts or disconnects.
- Emphasize the importance of closing the workbook properly when connectivity issues arise.
While a perfect solution might not exist due to inherent co-authoring limitations, these strategies can help you reduce the frequency and impact of workbook reversions. You can choose the approach that best suits your group's workflow and technical capabilities.
waygar You could use the trick I implemented in the attached file. Note that there is code in both Module1 and in ThisWorkbook. What I don't like about the solution is that the mouse cursor flashes every time the code runs. You may want to make it run less frequent by adapting the Timevalue argument.
- Hi Jan,
Works like a dream. Thank you.
