Roundhouse custom software development London


Sage NL Contra and Reversal


Nominal Journal and CB Import for MS Excel (Sage 200)

Summary of Feature


The Roundhouse MS Excel file import for Sage 200 Journal and CB txns is desktop program file which will connect to Sage 200 validate and import NL Journals for one or more Sage 200 companies. The Journal file is an MS Excel workbook (.xlxs), with a worksheet named ‘S200 Jnl’. The user may also select to import prior year Journals and also CB Nominal Transactions (including VATable). The CB NL Txn import is a different file format to the NL Journal with a worksheet named ‘S200 CB’


The program allows the user to browse and then validate an MS Excel file containing one or more Journals for one or more Sage 200 companies. 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.


The program may also use a CSV file containing the sage 200 company db names (with a new command line argument /m OpenPeriod (or /m ClosePeriod) /p "c:\Sage\Import\S200 Period End DEMO.xlsx" to open/close periods in Sage 200 (if permitted)


MS office and the Sage 200 desktop client must be installed.


S200 Nominal Journal/ CB Txn 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).


If the file is to be posted as a prior year Journal, then tick the ‘Journal is for Prior Year’. This will only be permitted if the NL settings allows prior year Journals. The date cannot be earlier than the last years end date.

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.

Field Mappings (CB Txn)

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.

Maintain Period for MS Excel (Multi-company)


This may be run from a desktop shortcut. A form will be displayed and the user will be able to confirm the maintain period action. The process will close the period or open a period instead (e.g. after a year end) based on the command line argument and list of companies and dates in the MS Excel file. All modules for closing/opening may also be processed.


The routine can be used to open a period (if closed ). They can be used after the users have completed the year end postings.


/m OpenPeriod /p "c:\rSage\Import\S200 Period End DEMO.xlsx"


Here is a sample MS Excel file with the Company names and Period date. The worksheet must be named SS200 Period Mnt” and have the column headings on the first row (as below)

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

Roundhouse bespoke software development London