NOMINAL JOURNAL AND CB IMPORT

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 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’. A Journal with VAT may also be posted. The worksheet for VATable Journals has a different format and is called ‘S200 JNl VAT’. 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.

If you wish to run the NL Journal on a regular basis (unattended), the program can be invoked with a command line argument /m NLJournal /p "c:\Sage\Import\S200 NL Jn DEMO.xlsx" to validate/post in Sage 200 (if permitted)

The program may also use a MS Excel 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)

The program may also use a MS Excel file containing the details of new NL accounts to be imported (with a new command line argument /m NLAccount (e,g, /p "c:\Sage\Import\S200_NominalAccounts.xlsx" to create new NL accounts in Sage 200. To also allow existing accounts to be updated use the /m NLAccount /a option.

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

Sage 200 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. NB: An ‘auto’ run option is also permitted when the command line arguments /m NLJournal and /p {file name} are provided.

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).

If the JNL does not balance (perhaps by a small 1p or 2p difference, then the user may invoke the program with an ‘autocorrect’ option. To ‘auto correct’ small values on the JNL (e.g. set a tolerance of -/+ 2p) by adjusting if JNL does not balance then adjust the last line in the JNL on the csv file to correct and make the NL balance. Use the command line argument for tolerance (e.g. /t 0.02 ). This will be reported on the MS XLS file.

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 (Nominal VAT)

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. S200 CB

Rows with a Goods values of zero will be ignored.

The NL may be group by adding a value to group the rows in the 2nd Ref (otherwise leave blank).

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)

Import NL Accounts from 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 import NL Accounts action. The process will import a list of new accounts across the specified sage 200 company’s) based on the command line argument and list of companies and NL account details in the MS Excel file. The Update of NL accounts is invoke with the /a option (see below)

/m NLAccount /p "c:\Sage\Import\S200_NominalAccounts.xlsx" /a

Here is a sample MS Excel file with the Company names and NL Account details. The worksheet must be named ‘s200_NominalAccount’ and have the column headings on the first row (as below)

Import PL Accounts from 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 import PL Accounts action. The process will import a list of new accounts across the specified sage 200 company’s) based on the command line argument and list of companies and PL account details in the MS Excel file. The Update of PL accounts is invoke with the /a option (see below)

/m PLAccount /p "c:\Sage\Import\S200_SupplierAccounts.xlsx" /a