The next step is to establish a link from the data in SharePoint to Excel.

Start by opening the calculation in Excel by clicking on the calculation file.

Once Excel opens, make sure you enable Macros in the document:

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

Move to a library and click image. Once clicked, you will get a similar popup. 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:

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 by the user. Click Enable, select New worksheet from the follow up dialog and click OK:

You should see a new sheet named Query appear in the calculation file that contains your lease data (Feel free to rename the new sheet for convenience).

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 calculation file with the sheet containing your leases.

Beware that you don’t need to repeat this process every month. You only need to do it once to create the first calculation file. For next reporting period, you can then copy the previous calculation folder by clicking the cogwheel on the folder and selecting the Copy with files option:

Then, you can simply update the title and period to the new one you work with. The copied calculation will have this connection in the file. And when you click the Update button on the Menu sheet, fresh lease data will be downloaded directly into the calculation.

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 calculation Excel spreadsheet.

19.2.1 Warning about disconnected workbooks (Warning ID 605)

For some reason, we’ve had issues with customers using disconnected workbooks. I.e. the tables are no longer connected to a source (SharePoint’s library). It might be a good idea to warn the user about this. In the 2020 version of the calculation file, there will be warnings that tell users if there exist tables/ workbooks that are disconnected from SharePoint and that lists all the disconnected tables.

If a customer has tables that are disconnected from SharePoint when they update leases, they’ll see a pop-out window, warning them that there are data sources disconnected from SharePoint.

After clicking OK in the warning tab, they’ll be able see which worksheets are disconnected from SharePoint as shown below.

The customer will then need to delete the disconnected worksheets that are listed in the warning and relink the libraries in the SharePoint to the calculation model.