Log in | Register



RunWorkbook

This sample, which is saved as RunWorkbook in the Samples subfolder of the Resolver One folder on your start menu, shows how to use the RunWorkbook function to run external Resolver One documents, overriding values in specific cells or cellranges, and using their results in your calculations. That way, you can put common functionality in a standalone Resolver One file and use it from many other documents, without the need of copy-pasting.

There are many different ways to use the RunWorkbook API. This sample demonstrates most of them. For more information, consult the RunWorkbook documentation in the Library documentation.

Getting values from external documents

The basic usage of RunWorkbook looks like so (cell B8):

=RunWorkbook("trades.rsl")

This will recalculate the file "document.rsl", and return a Workbook instance. You can then examine the contents of that workbook by using the same syntax used in user code to access the current workbook (cells C2:C6):

=$B$8['Trades']['Total Price', 'Total'] * (1 + B2)

You can also access named ranges in remote workbooks as attributes, as demonstrated in cell F10:

=F8.last_price

Overriding values in external documents

Instead of just using the saved values of an external document, you can override values in it, before recalculating. This will have the same effect as if you open that file, type in the desired values in the specified cells, then saving. Instead, when you use RunWorkbook, the remote file isn't changed in any way.

There are many different ways to specify the override values, each one useful in a different context:

Overriding with dictionaries

As demonstrated in cells F8:F9, you can use a dictionary that maps locations to values as your overrides. The locations must in one of the following formats:

  • A string that points to a named range or cell
  • A tuple in the format ('sheet', 'cell'), eg. ('Sheet1', 'B3')
  • A tuple in the format: ('sheet', col, row), where col and row can be either numbers, headers or in the case of col, a column name.

You can see an example of every format in the sample.

Overriding with cellranges

You can also use pairs of cellranges to override values, as demonstrated in B20:B22. Each pair must have two same-length, one-dimensional cellranges, the first one holding the locations (as specified above) and the second the values.

Overriding named ranges with keyword arguments

On top of the above syntax, you can also use keyword arguments to override named ranges in remote workbooks. You can see in the post-formula user code how the range named last_price is overriden:

wb = RunWorkbook('trades.rsl', sheet.F21, sheet.F22, last_price=10.69)

Caching external workbooks

Using external documents can be time-consuming, so you can use the useCache keyword argument to RunWorkbook to specify that you want to use the RunWorkbook cache.

=RunWorkbook("trades.rsl", useCache=True)

useCache defaults to False and has a dual meaning if it is True:

  • If the cache contains results for a particular workbook and set of overrides, RunWorkbook will use them without executing the workbook
  • If the cache is empty, RunWorkbook executes the workbook and stores the results in the cache

Comments