RunWorkbook

This topic demonstrates how to...

  • Use the RunWorkbook function to run an external workbook.
  • Temporarily override values in specific cells or cell ranges inside the external workbook, treating it like a user-defined function.
  • Use the results of the above in calculations.
  • Cache external workbooks to improve performance.

RunWorkbook enables you to put common functionality in a standalone Resolver One file and then use this from many other documents, without the need to copy and paste.

The example demonstrates most of many different ways to use the RunWorkbook API. For more information, consult the RunWorkbook topic in the Library documentation, or see more examples.

To run your workbooks from other .NET programs, see Resolverlib.

Before you begin

Do one of the following:

  • From Windows, select Start, Resolver One, Samples, RunWorkbook
  • Download the example from the Resolver Exchange.

Getting values from external documents

Cell B8 demonstrates the basic use of RunWorkbook:

=RunWorkbook("document.rsl")

This recalculates document.rsl, and returns a Workbook instance.

Cells C2:C6 show how you can examine the contents of the external workbook using the same syntax as required for accessing the current one:

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

Cell F10 is an example of accessing named ranges in external workbooks as attributes:

=F8.last_price

Overriding values in external workbooks

Sometimes it's useful to override the values in the external workbook so as to use the resulting calculations. This has the same effect as if you opened the workbook, entered new values and then saved the results... almost. RunWorkbook doesn't actually alter the contents. Effectively, the external workbook is treated like a user-defined function.

There are many different ways to specify the override values, each useful in different contexts.

Overriding with dictionaries

Cells F8:F9 get override values from a dictionary that maps locations to values. 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'), e.g. ('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—the example demonstrates all of these.

Overriding with cellranges

Cells B20:B22 use pairs of cellranges to override values. 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

The Post-formulae user code uses keyword arguments to override named ranges in remote workbooks. In this case the range last_price is overriden:

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

Caching external workbooks

You can cache external workbooks to enhance performance. Do this by using the RunWorkbook useCache keyword argument 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 uses them without executing the workbook.
  • If the cache is empty, RunWorkbook executes the workbook and stores the results in the cache.

Comments