Roundhouse custom software development London


Sage NL Contra and Reversal


Nominal Journal Import for MS Excel) (Sage 200)

Summary of Feature


The Roundhouse MS Excel file import for Sage 200 Journal is desktop program file which will connect to Sage 200, validate and import one or more NL Journals for one or more Sage 200 companies from a single MS Excel worksheet. The Journal file will be import from an MS Excel workbook (.xlxs), using a fixed format worksheet named ‘S200 Jnl’.


A windows program will allow the user to browse and then validate an MS Excel file. If valid then the journal(s) in the MS Excel file will be posted in Sage 200. If any journals are not valid then the MS Excel error cell for the Journal will show the error(s) so the user may review and correct.


Systems Options/Requirements


The new program will work with Sage 200c. MS office and the Sage 200 desktop client must be installed.


S200 Nominal Journal Import for MS Excel (Multi-company)


The S200 Nominal Journal Import for MS Excel will be run by clicking a desktop shortcut. The user must also have the Sage 200 desktop client installed (and MS Office – Excel). The Import form will have an ‘ok’ button to allow the user to select a MS Excel file containing the Journal worksheet.


The option to ‘validate’ may be used to check the Journals balance. To post the Journals to Sage 200 choose ‘Validate and import records’.


The file will be validated after the user has opened the file (any errors will be displayed on the form). A progress bar will show the validation progress (based on the number of rows).

The validation process will check the following details:

• S200 Company Name (and connection is OK)

• Transaction Date

• Nominal Account Specification

• Journal must balance


As single Journal is determined by the following fields:

• Company

• TransactionDate

• Reference


The journals will be marked in the MS Excel using a sequence reference (e.g/ J1, J2 …) during the validation. If any errors occur then the error will be set in the ‘Error’ cell for the Journal line.


Once the validation has completed, if any ‘error(s)’ have been found then a message box will be displayed.


If no errors are found on the validation, then the Journal ‘listbox’ control will be cleared and the Journal information will be displayed on the form. The total number of journals will be displayed.


When the option to ‘Validate and import records’ is selected the Journals are posted and the Sage 200 assigned URN is displayed on the form.

A sample MS Excel Journal (for one company) is shown below. The user should leave the ‘Status’ and ‘Error’ field blank (these are used by the Import program).

Once the file has been validated (or posted). The user may select another MS Excel file to process. The files will remain in the folder. The user may move (or remove) after posting to Sage 200.

Field Mappings (Nominal)

The MS Excel file must have the follow columns. The first column contains the field names.

Rows with a Goods values of zero will be ignored.

For further information on this and other Roundhouse Add-ons for Sage 200, please contact Roundhouse.

Roundhouse bespoke software development London