There must always be a link between SharePoint and the Excel file. If a new library is established, or if a contract is placed in a library that’s not linked to the calculation file, a link must be established. Here’s how to create a link from SharePoint to the Excel file: Keep the spreadsheet you’re going to use open in Excel (And avoid having many other files open). When Excel opens, make sure that you enable macros in the document. To do this, right-click on the file and click on “Properties”. Thus, tap on “Unblock”, then tap OK.

Then go back to your browser and go to the library to download data from SharePoint in an Excel format.

Press the button at the bottom of the library to be linked, as shown below:

Once clicked, you will get a similar pop-up. How it appears depends on your browser:

Click OK to proceed. You’ll either have a file automatically downloaded and saved in your downloads folder, or you’ll see a question like this:

image

Open the file from your downloads or click Open to open it directly without saving.

Once loaded, Excel should appear in a new window and ask this question:

This informs you that the file uses data connections, which means that the data in the table reads from SharePoint and that this must be cleared with the user. Click Enable, select New worksheet from the follow up dialog and click OK:

You should now see a new sheet named Query appear in the calculation file that contains your contract data. We recommend renaming this sheet with the name of the SharePoint library.

If, by any chance, this sheet was added to the wrong Excel file, you can easily move it to the correct file by right-clicking the sheet and selecting Move or Copy… from the menu. Select the correct destination book in the dialog, and confirm the move by clicking OK:

Once this is done, you should now have a contract file with the sheet containing your contracts. When you click the Update contracts button on the Menu sheet, fresh contract data will be downloaded directly form the SharePoint library into the Excel contract file.

If you wish to extract data from several libraries into the same calculation, you must enter each library and repeat the process above. Copy and paste into the same contract Excel spreadsheet.

NB! You only need to do this once when setting up a link. Once the link is established, after clicking the Update contracts button, the Excel contract file will automatically update new contracts and changes to the existing contracts in the library. You can delete a link by deleting the library sheet where the information appears.

9.1.1 Warning about disconnected workbooks (Warning ID 605)

In the contract file, there are alerts that notify users if there are spreadsheets that are not connected to SharePoint. A list of all offlne spreadsheets is then displayed.

If you have spreadsheets that are not connected to SharePoint when you update leases, you’ll see a pop-out window, warning you that there are data sources that are not connected to SharePoint.

After pressing OK, you’ll be able see which spreadsheets are disconnected from SharePoint, as shown below.

You’ll need to delete the offline spreadsheets listed in the warning sheet tab and re-establish the link between the SharePoint libraries and the calculation file.