Unattended Runs and Non-Interactive Defaults

When you drive Excelerator from a VBA macro rather than from the ribbon, there is nobody at the keyboard to answer the questions Excelerator would normally ask — confirmation prompts such as "do you want to overwrite the data on the sheet?", and any warnings raised while validating or saving.

Excelerator therefore runs in non-interactive mode, in which it answers those questions itself. By default it carries on past every confirmation, and accepts each warning's own default option.

You can change those answers by creating a small settings file for the module. This is useful when the answer a person would give at the keyboard is not the answer you want from an overnight run — for example, saving a batch of sales orders without also emailing acknowledgements to every customer.

Info

Non-interactive mode applies only when Excelerator is invoked from code, through InvokeSaveMethod or InvokeValidationMethod. Working normally in Excel is unaffected, and so is any module you have not created a file for.

The settings file

Create one file per module in:

%AppData%\Codis\Excelerator\Settings

This is the same folder Excelerator already uses for your other saved settings. Name the file after the module, exactly as the module appears on the Excelerator ribbon:

NonInteractive.<Module>.json

So the Sales Order module's file is NonInteractive.Sales Order.json, and the Stock module's is NonInteractive.Stock.json.

Warning

%AppData% belongs to the Windows user account that runs the macro. If your automation runs as a scheduled task or a service account, place the file in that account's %AppData%, not in your own — otherwise Excelerator will not find it and will fall back to its built-in answers.

If a module has no file, Excelerator behaves exactly as it always has. You only need files for the modules whose answers you want to change.

Action names, confirmation identifiers, warning identifiers and the answers themselves are all matched without regard to capitalisation, so savebatch and SaveBatch are equivalent.

Info

Excelerator reads each module's file once, the first time that module runs unattended, and keeps it for as long as Excel stays open. After you create or change a file, close and reopen Excel before the next run, or the run will still use the previous version. This applies also to a file that was rejected and then corrected: Check Non-Interactive Defaults reads the file again each time, so it can report the corrected file as valid while the open Excel still has the rejected one.

Letting your editor check the file as you type

Most editors — Visual Studio Code, Visual Studio, Notepad++ with its JSON plugin — will check a JSON file for you if you tell them which rules it follows. Add this as the first line inside the file:

"$schema": "https://sage-200-excelerator.help.codis.co.uk/schemas/non-interactive-defaults.schema.json",

The editor then underlines a misspelled setting or an answer that is not allowed while you are still writing the file, instead of leaving you to discover it in the error log after a run. Excelerator itself takes no notice of the line.

Getting a starter file

You do not have to write the file from scratch. Your Codis consultant can create a starter file for any module, and check a file you have written, from two commands on that module's Tools menu. These commands are available to Codis consultants only.

Generate Non-Interactive Defaults

Generate Non-Interactive Defaults writes a starter file that lists every confirmation and every warning the module can raise. Each entry shows the buttons or options the prompt offers, so you can see what each one can be changed to, and is filled in with that prompt's own default answer. A warning that offers no options is listed with no answer, so you can see that it can be answered.

A starter file also sets the warnings default to continue. It does not set a confirmations default.

A starter file left unedited answers each prompt with its own default, much as a run with no file does. You then change only the entries you want answered differently, and delete any you do not need.

Before it writes the file, Generate asks whether to Apply my saved warning answers. The box starts unticked. When it is ticked, any warning you have previously answered with "don't ask me again" in this module is filled in with your saved answer instead of its default.

Warning

Saved warning answers are shared by every module and matched by the warning's identifier alone. If two modules raise a warning with the same identifier — for example OrderMissingLines, raised by both Purchase Order and Sales Order — an answer you saved in one can be carried into the other's file. Review the warning entries after generating with this box ticked.

Generate then shows a save dialog. Accept the suggested location, %AppData%\Codis\Excelerator\Settings\NonInteractive.<Module>.json, to use the file on this computer, or save it elsewhere to copy to the computer that runs the macro. You are asked before an existing file is overwritten.

Check Non-Interactive Defaults

Check Non-Interactive Defaults reads the module's file from the settings folder, exactly as an unattended run would, and reports one of:

Result	Meaning
No file	The module will use Excelerator's built-in answers. This is normal.
Valid	The file will be used. The report counts the confirmation and warning answers it found.
Rejected	The file will be ignored. The report names the setting or value that is wrong, and the answers the prompt accepts.
Unreadable	The file could not be opened this time — for example, because it is open in another program. Nothing is wrong with its contents.

A valid file can also carry cautions. A caution does not stop the file being used, but tells you that part of it will have no effect — for example, an identifier this module never raises, or a choose value the prompt does not offer.

A worked example

This file tells the Sales Order module to save unattended, but not to print or email acknowledgements:

{
  "$schema": "https://sage-200-excelerator.help.codis.co.uk/schemas/non-interactive-defaults.schema.json",
  "module": "Sales Order",
  "warnings": { "default": "continue" },
  "actions": {
    "SaveBatch": {
      "confirmations": {
        "byId": {
          "SalesOrder.PrintAcknowledgements": { "buttons": [ "Yes", "No" ], "choose": "No" }
        }
      }
    }
  }
}

Reading it from the top:

module — the module this file applies to. For your own reference; the file name is what Excelerator matches on.
confirmations and warnings at the top level apply to the whole module.
actions lets you override those answers for one menu action. SaveBatch is the action a macro calls to save; ValidateBatch validates. These are the same names you pass from VBA.

So every warning in this module is accepted, and the acknowledgements prompt during a save is answered No. Every other confirmation keeps Excelerator's built-in answer.

Answering confirmations

A confirmation is a yes/no or OK/cancel question. The answers are:

Value	Effect
`Yes`	Proceeds, as if the user clicked Yes
`OK`	Proceeds, as if the user clicked OK
`No`	Does not proceed
`Cancel`	Does not proceed

Answering one particular confirmation

Use byId to answer a named confirmation. Each entry has:

choose — the button to press.
buttons — the buttons the prompt offers. This is for your reference only. Excelerator does not read it, so it does no harm if a later release changes the prompt.

"confirmations": {
  "byId": {
    "SalesOrder.PrintAcknowledgements": { "buttons": [ "Yes", "No" ], "choose": "No" }
  }
}

Answering every other confirmation

Set default to answer every confirmation that has no byId entry:

"confirmations": {
  "default": "Yes",
  "byId": {
    "SalesOrder.PrintAcknowledgements": { "buttons": [ "Yes", "No" ], "choose": "No" }
  }
}

Use default with care. Some prompts offer Yes and No, others OK and Cancel, and no one answer suits both: a default of Yes is ignored by every OK/Cancel prompt, with a note in the error log each time. Naming each prompt in byId avoids this, and is what a starter file does.

Info

The answer must be one the prompt actually offers. A Yes/No prompt cannot be answered "OK", and an OK/Cancel prompt cannot be answered "Yes" — Excelerator ignores an answer the prompt does not offer and notes it in the error log.

To set Only Process Unsaved? for an unattended run, use the ordinary Excelerator option of that name (see Tracking the Valid and Saved Status of Data). It is not part of this file.

The save confirmation

When you click Save to Sage in Excel, Excelerator shows the data it is about to save and asks you to confirm. In an unattended run, that confirmation is answered OK unless the file names it:

"byId": {
  "Excelerator.SaveConfirmation": { "buttons": [ "OK", "Cancel" ], "choose": "OK" }
}

The save confirmation answers only to its own byId entry. A confirmations default never applies to it, so a default of Cancel stops the prompts raised during the run without also stopping every save.

Confirmations you can name

A starter file lists the confirmations its module raises. The full list is:

Identifier	Buttons	Raised by
`Excelerator.SaveConfirmation`	OK, Cancel	Every module, when saving
`Excelerator.ClearUpdateStatusRange`	OK, Cancel	Every module
`Excelerator.ClearSheet`	Yes, No	Customers, Suppliers, Stock, Stock With Manufacturing and Tax Settlement, when clearing the sheet
`Transactions.ClearSheet`	Yes, No	All other modules, when clearing the sheet
`FileSave.OverwriteExistingFile`	Yes, No	Every module, when the Save to File option is on
`Printing.ConfirmPrint`	OK, Cancel	Modules that print
`Master.PopulateOverwrite`	Yes, No	Customers, Suppliers, Stock and Stock With Manufacturing, when downloading
`Master.DeleteRecords`	OK, Cancel	Customers, Suppliers, Stock and Stock With Manufacturing, when deleting
`Order.CalculateValues`	Yes, No	All sales and purchase order processing modules (orders, quotations, returns, pro formas and invoicing)
`Order.RedownloadAfterDelete`	Yes, No	All sales and purchase order processing modules (orders, quotations, returns, pro formas and invoicing)
`Order.RedownloadAfterLineDelete`	Yes, No	All sales and purchase order processing modules (orders, quotations, returns, pro formas and invoicing)
`SalesOrder.PrintAcknowledgements`	Yes, No	Sales Order and AI Sales Order
`AISalesOrder.ClearBeforeRead`	Yes, No	AI Sales Order
`Ledger.CalculateValues`	Yes, No	Sales and purchase ledger invoice modules
`LedgerAllocation.AutoAllocate`	Yes, No	Ledger allocation modules
`CBReceipts.CalculateValues`	Yes, No	Cash Book receipts and payments
`TaxSettlement.AutoAllocate`	Yes, No	Tax Settlement
`NominalReconciliation.ClearSheetOnDownload`	Yes, No	Nominal Reconciliation
`POPGRD.PopulateOverwrite`	Yes, No	POP Goods Received
`POPGRD.PopulateSkipUnloadable`	Yes, No	POP Goods Received
`POPInvoice.PopulateOverwrite`	Yes, No	POP Invoice
`POPInvoice.PopulateSkipUnloadable`	Yes, No	POP Invoice
`PriceBookDiscount.ClearSheetOnDownload`	Yes, No	Price Book Discount
`PriceBookDiscount.ClearSheetOnSelectStock`	Yes, No	Price Book Discount
`Stock.ClearSheetOnWizardDownload`	Yes, No	Stock and Stock With Manufacturing
`Stock.IncludeLocations`	OK, Cancel	Stock and Stock With Manufacturing
`Stock.DownloadWithoutBinRange`	OK, Cancel	Stock and Stock With Manufacturing
`StockAdjustments.Populate`	Yes, No	Stock Adjustments
`StockAdjustments.ClearSheetOnDownload`	Yes, No	Stock Adjustments
`StockAdjustments.ClearSheetOnStockTakeDownload`	Yes, No	Stock Adjustments
`StockTake.CompleteStockTake`	Yes, No	Stock Take
`StockTake.DeleteStockTake`	Yes, No	Stock Take
`StockTake.ClearSheetOnEnterValues`	Yes, No	Stock Take
`StockTake.PopulateActualFromExpected`	Yes, No	Stock Take
`StockTake.IdenticalNominalCodes`	Yes, No	Stock Take
`StockTake.DownloadAfterCreate`	Yes, No	Stock Take
`SupplierPriceList.Populate`	Yes, No	Supplier Price List
`SupplierPriceList.Redownload`	Yes, No	Supplier Price List
`SupplierPriceList.DeleteItems`	OK, Cancel	Supplier Price List

Any confirmation without an identifier is covered only by default.

Answering warnings

A warning is a message you can choose to accept or reject, sometimes with a choice of options. Set default to one of:

Value	Effect
`continue`	Accepts the warning and carries on
`abort`	Stops rather than continuing

Answering one particular warning

Use byMessageId to treat a single warning differently:

"warnings": {
  "default": "continue",
  "byMessageId": {
    "SL0123": { "continue": "abort" },
    "ExistingOrderWithoutLineNumbers": {
      "options": [
        { "id": "Continue", "description": "Continue to save this Order" },
        { "id": "Ignore", "description": "Continue but ignore this Order" },
        { "id": "IgnoreAll", "description": "Ignore All" }
      ],
      "choose": "Ignore"
    }
  }
}

For each warning you can set:

continue — continue or abort, just for this warning. Leave it out to use the default.
choose — the option to choose when the warning offers a choice. Use an id from the warning's options.
options — the options the warning offers. This is for your reference only. Excelerator does not read it.

A starter file fills in choose and options for every warning but leaves continue out, so the default still decides whether the run carries on.

Finding a warning's identifier

A starter file lists every warning its module can raise. To find the identifier of a particular warning yourself, validate the sheet in Excel as normal. The Validation Results window lists every message, with its identifier in the ID column and the rule that raised it in the Validator column.

Use the ID on its own, as in the example above. If two different validators happen to use the same ID and you need to tell them apart, write the key as Validator|ID — for example "SalesOrderPriceValidator|SL0123".

Info

If you choose an option that no longer exists — because the options for that warning changed in a later release — Excelerator falls back to that warning's normal default option rather than failing. It is worth running Check Non-Interactive Defaults on your files after an upgrade.

Which setting wins

Excelerator looks for an answer in this order and uses the first one it finds:

The named confirmation or warning, inside actions
The default, inside actions
The named confirmation or warning, at the top level
The default, at the top level
Excelerator's own built-in answer

Confirmations and warnings are resolved separately, and so are the individual settings within them. You can set choose for a warning without setting continue; anything you leave out simply falls through to the next level.

The save confirmation is the one exception: it uses steps 1, 3 and 5 only, and never a default.

If the file has a mistake in it

Excelerator checks the whole file before using any of it. It is rejected if it is not valid JSON, if it contains a setting name Excelerator does not recognise (a misspelling such as confirmationz), or if any answer is not one of the accepted values (Maybe instead of Yes, stop instead of abort).

When a file is rejected, Excelerator writes the reason to the error log, naming the file, and carries on using its built-in answers — the same answers you would get with no file at all. Your run is not interrupted and your data is not put at risk, but none of your settings take effect.

Warning

Because a rejected file is simply ignored, an unattended run that appears to succeed may not have used your settings at all. After creating or editing a file, have it checked with Check Non-Interactive Defaults, or look in the Excelerator error log once to confirm it was accepted. Adding the $schema line described above catches most mistakes before you get that far.

A file that is simply absent is not a mistake — that is the normal case, and Excelerator uses its built-in answers.

Info

A good way to check a new file is to run your macro against a small test sheet in a test company first, and confirm it behaves as you expect before pointing it at live data.

After a macro has run, saves you make from the ribbon in the same Excel session are not asked and use Excelerator's built-in answers. Close and reopen Excel before you work on the sheet by hand.