Using Modano linked workbook projects with OneDrive or SharePoint

Hi Guys,

We've had an increasing number of users experience issues trying to use multi-linked workbook projects from OneDrive or SharePoint, so we wanted to add this post for future reference.

Recommended Approach

Put simply, you need to work locally when building or using Modano multiple workbook projects, even if the files themselves are stored on OneDrive or SharePoint (or any other cloud storage system for that matter). This is to prevent workbook links breaking or becoming corrupted.

Hence, as a general rule, we recommend working from your local synced folders and letting Windows Explorer handle the syncing of OneDrive or SharePoint (etc.) in the background. We've done this internally for the past decade and never had an issues. Here's some good resources from Microsoft about this:

1. Sync SharePoint files with the OneDrive sync
2. Sync SharePoint files and folders

If you're not using linked workbooks, you should be able to open and save files from/to SharePoint directly too. We also have no issues doing this but it's not our default way of operating as we have no issues working locally and never concerning ourselves with syncing.

OneDrive Settings

Importantly, there is a file collaboration setting in OneDrive which, if switched on, will result in your Offices always opening directly from OneDrive or SharePoint, even if opened from Windows Explorer. So this setting must be switched off.

To switch off this setting, right-click on the OneDrive icon in your task bar, click Settings from the OneDrive menu, then select the Office tab within the Microsoft OneDrive dialog. Then uncheck the check box Use Office applications to sync Office files that I open, as shown below:

With this setting switched off, Excel workbooks opened from local directories will stay local and not cause issues opening from OneDrive or SharePoint.

Note that you may need to speak with your IT administrator if your organization has turned this setting on and you do not have sufficient privileges to turn it off.

You can also use the Open without Modano command (Modano tab, Workbook menu, Open without Modano) to open workbooks without Modano loading them, if you'd like to use multi-linked workbooks with this setting switched on, but there will always be a risk of workbook links becoming corrupted to we recommend against doing this.

Co-Authoring

Regarding co-authoring, changes to workbooks made using automation (i.e. Modano) are not supported by co-authoring. Hence, if you want to co-author, we recommend making your models Excel Online compatible (which basically means removing controls and any other content not supported by Excel Online) and then co-authoring in Excel Online - i.e. in the browser. 

In a dream world you could co-author develop models, but in reality we've found co-authoring is primarily valuable for facilitating the entry of assumptions by multiple people, and this is supported well by Excel Online.

The same general comments apply to Teams, although we generally avoid using Excel files within Teams. I'd be interested to hear your use case for this...

Hope this helps guys.

Michael
Managing Director | Modano