BAAN ERP

OLE with BAAN ERP User Manual

A publication of:

Baan Development B.V.

P.O.Box 143
3770 AC Barneveld
The Netherlands

Printed in the Netherlands

Baan Development B.V. 1998.
All rights reserved.

The information in this document

is subject to change without
notice. No part of this document
may be reproduced, stored or
transmitted in any form or by any
means, electronic or mechanical,
for any purpose, without the
express written permission of
Baan Development B.V.

Baan Development B.V.

assumes no liability for any
damages incurred, directly or
indirectly, from any errors,
omissions or discrepancies
between the software and the
information contained in this
document.

Document Information

Code: U7027A US
Group: User Documentation
Edition: A
Date: March 1998
Table of contents

1 Introduction 1-1
Definitions, acronyms, and abbreviations 1-1
Overview 1-2
2 OLE installation 2-1
Introduction 2-1
Installing BAAN OLE 2-1
Install Excel .xls and .xla files 2-1
To reference Baan.xla 2-1
3 Using OLE with BAAN ERP 3-1
Application worksheet controls 3-1
Implementation guidelines 3-2
Getting started 3-5
All applications 3-5
MPS forecasts 3-5
Financial budgets 3-6
Sales budgets 3-6
4 Worksheets 4-1
Introduction 4-1
InputSpecs worksheet 4-2
BaanOle module sheets 4-3
BAAN button subroutine 4-3
Build worksheet subroutine 4-5
Input specification worksheet 4-6
Error worksheet 4-7
5 Technical information 5-1
Baan.xls worksheet subroutines 5-1
StartProcess 5-1
EndProcess 5-1
PrintReport 5-2
Errorsheetsub 5-2
PrinterrorinExcel 5-2
Printtotals 5-3
Processallrecords 5-3
CreateRecord 5-4

OLE with BAAN ERP User Manual

i
Table of contents

ConnectBaan 5-4
DisconnectBaan 5-4
SendtoBaan 5-5
ValidateRanges 5-5
Checkinputspec 5-6
BuildWorksheet 5-6
Baan.xls worksheet functions 5-7
Getarg 5-7
Getlastarg 5-7
CvtString 5-7
Colnumtoletter 5-7

OLE with BAAN ERP User Manual

ii
About this document

This document provides the following information about the OLE with
BAAN ERP product:
n Chapter 1 includes basic introductory information, a list of definitions,
acronyms, and abbreviations used in this document, and an overview of OLE
with BAAN ERP.
n Chapter 2 provides an installation procedure.
n Chapter 3 explains how to use OLE with BAAN ERP to upload Excel data
into a BAAN application.
n Chapter 4 provides basic information about the content and purpose of
worksheets.
n Chapter 5 provides technical information about worksheet subroutines and
functions.

OLE with BAAN ERP User Manual

iii
About this document

OLE with BAAN ERP User Manual

iv
1 Introduction

The OLE tool allows you to transfer data between BAAN applications and Excel
worksheets. You can use the OLE module in the BAAN Utilities package to
control and report on OLE functions. You can import OLE data into the
following BAAN functions:
n Financial Budgets
n Sales Budgets
n MPS Forecasts
Sample spreadsheets for these functions are provided as Excel worksheets. You
can maintain and manipulate data in these worksheets and then upload the data
into a BAAN application.

Definitions, acronyms, and abbreviations

Excel
Microsofts OLE-compliant PC spreadsheet product.
Excel worksheet
An individual page of an Excel workbook (spreadsheet) that contains data or
programming logic. Worksheets are used in the transfer of OLE data to BAAN
applications.
Input specification sheet
A worksheet in Excel that contains the specifications to build an application
worksheet. This sheet contains the appropriate name settings for columns, rows,
and ranges. The input specification sheet provides mapping from Excel fields to
BAAN application fields and can be customized to meet specific data
requirements.
OLE
An object-oriented technology designed for creating, arranging, and accessing
object-based components across process boundaries and machine boundaries.
Upload
Data transferred from one system to another by means of an automated process.
VBA (Visual Basic for Applications)
Microsofts subset of the Visual Basic programming language that is used for
Excel.

OLE with BAAN ERP User Manual

1-1
Introduction

Overview
The implementation and use of OLE with BAAN ERP requires Microsoft
Windows 95 or higher or Windows NT. The BW driver is the connection for
OLE with BAAN ERP. In addition, you must have Microsoft Excel loaded.
OLE with BAAN ERP allows you to maintain budget data and forecast data on
your PC and use this data to update the appropriate BAAN application. The
application areas include:
n Financial Budgets: you can upload budget data to add new data or replace
existing information.
n Sales Budgets: you can upload sales budget data to add new data or replace
existing information.
n MPS Forecasts: you can upload MPS forecast data to add or replace
information for an existing MPS plan. You cannot create a new plan using the
upload function.
Download capabilities exist with information being returned from BAAN as
codes, messages, and error reporting. However, you cannot download existing
BAAN application records into Excel.
To perform error reporting in the BAAN application, you must use a report
window. You can also send the error report back to Excel where it is stored in a
worksheet called ERROR, or you can print the error report with a print session.
To start the update process, use a job scheduling function.

OLE with BAAN ERP User Manual

1-2
2 OLE installation

Introduction
This chapter describes the procedure used to install OLE with BAAN ERP.
The prerequisites for installation are:
n The BW driver must be installed; see the BW Installation Guide for details.
n Microsoft Excel must be installed.
n The files from the $BSE/BaanOle/* directory must be installed.

Installing BAAN OLE

The following example uses the MPS Forecast workbook. You can also initiate
the procedure from the Financial Budget workbook or the Sales Budget
workbook. After you have referenced the Baan.xla file, you do not need to repeat
the procedure.

Install Excel .xls and .xla files

Place the following files in the Excel directory:
n FINBUDG.xls: Financial Budgets workbook
n MPSOLE.xls: MPS Forecast workbook
n SLSBUDG.xls: Sales Budget workbook
n Baan.xla: BAAN subroutines that work with the application workbooks
n Baan.xls: Source code for Baan.xla

To reference Baan.xla
1 Launch Excel.
2 Click Open on the File menu.
3 Open the MPSOLE.xls workbook.
4 Click the BaanOle worksheet tab.

OLE with BAAN ERP User Manual

2-1
OLE installation

5 Click References on the Tools menu (as displayed in Figure 2-1 below).

Figure 2-1, Tools menu in BaanOle worksheet

OLE with BAAN ERP User Manual

2-2
 OLE installation

6 When the References dialog box shown in Figure 2-2 appears, scroll to find
Baan.xla and select it. If you cannot find Baan.xla, click the Browse button to
search your drive(s).

Figure 2-2, Reference dialog box

OLE with BAAN ERP User Manual

2-3
OLE installation

7 When the Add Reference dialog box shown in Figure 2-3 appears, choose the
BAAN icon with the small red identifier. This icon represents the Baan.xla
file. Click Open.

Figure 2-3, Add reference dialog box

OLE with BAAN ERP User Manual

2-4
 OLE installation

8 When the References dialog box shown in Figure 2-4 appears, you will see
that Baan.xla is checked. Click OK.

Figure 2-4, Reference dialog box

9 Click Save on the File menu.

The installation is complete.

OLE with BAAN ERP User Manual

2-5
OLE installation

OLE with BAAN ERP User Manual

2-6
3 Using OLE with BAAN ERP

This chapter explains how to use the application worksheets to upload data to
BAAN applications with the MPS Forecast workbook as an example. The
information is also relevant for the Financial Budget and Sales Budget
workbooks.

Application worksheet controls

Figure 3-1 shows an example of the MPS worksheet in the MPSOLE.xls file.
Before learning how to use the worksheet, you need to understand how the
buttons work.

Figure 3-1, MPS forecast worksheet

BAAN button
Click this button to start the upload/download process to the BAAN application.

OLE with BAAN ERP User Manual

3-1
Using OLE with BAAN ERP

Build Worksheet button

Click this button to build the Excel worksheet with the Input Specification sheet.
When completed, the worksheet displays all field names, and name ranges are
built for columns and rows.
Print Error Option buttons
Click BAAN to print error information from the BAAN application.
Click Excel to print error information on the Excel ERROR worksheet.

Implementation guidelines
Use the following instructions to build an MPS worksheet and upload data into
an MPS plan.
1 Open the MPSOLE.xls workbook.
2 Click the MPS worksheet tab. The MPS Forecast Worksheet appears (see
Figure 3-2).
3 Click the Build Worksheet button. The worksheet should now contain
column heading 2 (see Figure 3-3).

Figure 3-2, MPS forecast worksheet - example 1

OLE with BAAN ERP User Manual

3-2
 Using OLE with BAAN ERP

Figure 3-3 MPS forecast worksheet - example 2

Notice that although the worksheet has been protected, the cells below the
headings are available for entry. Columns and rows cannot be inserted.
You can enter data into the appropriate rows or columns, or copy and paste data
from other workbooks.
Refer to Figure 3-4.

OLE with BAAN ERP User Manual

3-3
Using OLE with BAAN ERP

In the MPS Forecast Worksheet (see Figure 3-4) you must highlight the
worksheet area that contains the multiple-occurring fields before you click the
BAAN button. An example of a multiple-recurring field is MPS Period. See
Chapter 4, Worksheets, for an explanation of multiple occurring fields.

Figure 3-4 MPS forecast worksheet - example 3

When you click the BAAN button, the highlighted area (shown in Figure 3-4)
will be uploaded to the BAAN application along with the other information in
columns A, B, C, D, and rows 9 through 11.

OLE with BAAN ERP User Manual

3-4
 Using OLE with BAAN ERP

Getting started
This section provides some additional information for using OLE with
BAAN ERP.

All applications
Use the following steps to upload data into BAAN applications:
1 If the InputSpecs have changed, click Build Worksheets to make or modify
the InputSpecs worksheet (see Chapter 4, Worksheets).
2 Determine if the results on the spreadsheet are correct.
3 If the results are not correct, modify the InputSpecs worksheet (see Chapter 4,
Worksheets), and rerun the Build Worksheets process.
4 Type or copy data into the worksheet.
5 Select a print error option by clicking the Excel or BAAN option button.
6 Click the BAAN button to start the upload process.

MPS forecasts
The following fields are included in the MPS Forecast worksheet:

MPS Forecasts Worksheet

Field Description
ADDREPLACE Add or replace forecast
SCENARIO Used as part of the key
PLANLEVEL Used as part of the key
PLANITEM Used as part of the key
PERIOD Used as part of the key
FORECAST The forecast amount

The InputSpecs worksheet is delivered with values in the Period, Start Value and
Increment columns. You need to change these values before uploading data to
BAAN. See Chapter 4, Worksheets, for instructions on making modifications to
the InputSpecs worksheet.
NOTE: Only existing MPS forecasts can be added to or replaced.

OLE with BAAN ERP User Manual

3-5
Using OLE with BAAN ERP

Financial budgets
The following fields are included in the Finance Budgets worksheet:
Finance Budget Worksheet
Field Description
Company Number Used as part of the budget key
Budget Number Used as part of the budget key
Year Used as part of the budget key
Account Number Account number
Dimension 1 - 5 Account dimension
Quantity 1 - 2 Quantity amount for budget
Period 1 - 12 Budget amounts per period; 12 is not a
limitation on the number of periods

Depending on a companys business, the Budget Periods can be different than

those supplied with the InputSpecs worksheet. Follow the instructions in Section
4.4, Input specification worksheet, for modifying the InputSpecs worksheet.
NOTE: The upload process will add new budget information or replace existing budget
information.

Sales budgets
There are five worksheets in the Sales Budgets workbook:
Sales Budget Worksheets
Field Description
Qty Quantities
AltQty Alternate quantities
AmtProfit Profit amounts
AmtDiscount Discount amounts
AmtTurnover Turnover amounts

OLE with BAAN ERP User Manual

3-6
 Using OLE with BAAN ERP

The following fields are included on the Sales Budget worksheets:

Sales Budget Worksheets
Field Description
ADDINSERT Add or insert
Sort Code Used as part of the sales budget key
Year Used as part of the sales budget key
Period Used as part of the sales budget key
Sales Rep An employee number
Area Sales area
Line of Business Line of business
Item Group Item group
Quantities Quantities for budgets
Alternate Quantities Alternative quantities for budgets
Turnover Amounts Turnover amounts
Discount Amounts Discount amounts
Profit Amounts Profit amounts

NOTE: All the fixed parts of the record are entered in the Qty worksheet.
All the variable parts of the record are entered on all the worksheets.

OLE with BAAN ERP User Manual

3-7
Using OLE with BAAN ERP

OLE with BAAN ERP User Manual

3-8
4 Worksheets

Introduction
This chapter describes the Excel worksheets supplied with OLE with
BAAN ERP.
These worksheets include:
Excel Worksheets
Worksheet Description
InputSpecs Contains the input specifications to build an
application worksheet with various data.
ERROR Contains error information about the field value,
column, row, and error message as well as printed
statistics for the read, processed, and rejected
records.
BaanOle A module sheet that contains the VBA code that runs
the specific application and the Build Worksheet
process.
Application-specific Contains all the fields and buttons necessary to run an
OLE with BAAN ERP application.

OLE with BAAN ERP User Manual

4-1
Worksheets

InputSpecs worksheet
The following table provides the cell name, cell description, and valid values for
the InputSpecs worksheet:
Input Specs Worksheet
Cell Name Cell Description Valid Values
Worksheet The name of the worksheet in a workbook.
Name The actual worksheet must exist in the
workbook. User defined.
Name The name used to map Excel information to Cannot be empty
Ranges BAAN information and to identify the field
name on the ERROR worksheet. User
defined.
Column The column number of name range. Can be Numeric only
put anywhere on the worksheet. User defined.
Row The row number where an additional field can Numeric or empty
be used as input. Use with type OR.
Rec The position in the record that is sent to Numeric only;
Sequence BAAN. This position must not be changed cannot be 1, 2, or 3
because record positions are used by the
BAAN DLLs. Because the first three record
positions are used internally in BAAN, the first
record sequence must be 4.
Type There are three field types: static fields on a
record, multiple occurring fields, and fields that
are on other rows.
If static, the value is a space, or the field can Empty (or space):
be left empty. static field
If multiple, the value is OC. OC: multiple
occurrences
The value for fields on other rows is OR. OR: On another
row
Number of A multiple occurring field must have a number Numeric or empty
Occurs greater than 1 to indicate the number of
periods that occur.
A field on another row (OR) that is related to a
multiple occurrence field (OC) will have a
number of occurrences.

OLE with BAAN ERP User Manual

4-2
 Worksheets

Start Value The value used for a date value that is used Empty or valid date
with a number of occurrences.
FillDate The information in this cell identifies whether Empty or
the date is a day, week, or month. This date is day/month/year
taken from Excel Fill/Series/Date and formats
all columns based on the number of
occurrences with a date value.
Increment The value that is added to the start value. Numeric or empty

BaanOle module sheets

BAAN button subroutine
DownLoadtoBaan is the main subroutine included in all BaanOle module sheets.
This subroutine controls the processes of assembling the records, running the
BAAN application, sending the data to the application, and printing error
information.
Use the following Excel parameters to pass various data to the various
subroutines. See Chapter 5, Technical information, for a list of the Baan.xls
worksheet subroutines.
n worksheetname is the name of the main worksheet if there is more than one.
n dllname is the actual DLL name that you must use for the specific BAAN
application.
n dllfname (function name) is the actual function with the DLL in BAAN.

OLE with BAAN ERP User Manual

4-3
Worksheets

Figure 4-1 shows the BaanOle module sheet for the MPS Forecasts workbook
and the DownLoadtoBaan subroutine.

Figure 4-1, DownLoadtoBaan subroutine

OLE with BAAN ERP User Manual

4-4
 Worksheets

Build worksheet subroutine

The subroutine included in all BaanOle module sheets is called BuildWks. This
subroutine controls the building of all the names of the worksheet and also
supplies formatted dates.
Use the following Excel parameters to pass various data:
n Worksheet name is the name of the main worksheet if there is more than one.
n Row number is the starting row number where the heading will be placed.
Figure 4-2 shows the BuildWks subroutine on the BaanOle module sheet.

Figure 4-2, BuildWks subroutine

OLE with BAAN ERP User Manual

4-5
Worksheets

Input specification worksheet

Review the InputSpec worksheet to determine if all necessary fields are
represented.
Figure 4-3 shows the InputSpecs worksheet from the MPSOLE.xls workbook.

Figure 4-3, InputSpecs worksheet

NOTE: If the values for Forecast and Period in the Number of Occurs column are
different, make any change necessary to ensure that these fields match. These
values must be exactly the same.
If the values for Start Value, FillDate, Increment, and Edit Rule are inconsistent,
change these values as well.
If anything is changed on the InputSpec worksheet, you must rebuild the MPS
worksheet by clicking the Build Worksheet button on the MPS worksheet.

OLE with BAAN ERP User Manual

4-6
 Worksheets

Error worksheet
The following table describes the information available on the ERROR
worksheet.

ERROR Worksheet
Data Description
Field name Value from the Name Ranges from the InputSpecs
worksheet
Field value Value from the cell
Column Actual column letter for the field value
Row Actual row number for the field value
Error Message Error message from BAAN application
Batch Number System-generated number; format is
YYMMDDHHMMSS
Excel Record Information
Data Description
Records Sent Number of records sent to BAAN. A record
consists of all fixed portions of a row and one or
more variable positions.
Records Processed Number of records that were accepted
Records Rejected Number of records that were rejected

OLE with BAAN ERP User Manual

4-7
Worksheets

Figure 4-4 is an example of the ERROR worksheet.

Figure 4-4, ERROR worksheet

OLE with BAAN ERP User Manual

4-8
5 Technical information

This chapter contains technical information about the Baan.xls worksheet

subroutines and functions.

Baan.xls worksheet subroutines

StartProcess
Description
This subroutine initializes record counters, establishes the batch number,
validates the InputSpecs with the name ranges, establishes the connection to
BAAN, and selects all the cells that are highlighted. The print in Excel or BAAN
is also determined.
Excel parameters
Worksheet name is taken from the BaanOle module sheet.
Subroutines called
ValidateRanges, ConnectBaan
Functions called
None

EndProcess
Description
This subroutine starts the print report subroutine and displays a message that
indicates the batch number and number of records sent.
Excel parameters
None
Subroutines called
PrintReport
Functions called
None

OLE with BAAN ERP User Manual

5-1
Technical information

PrintReport
Description
This subroutine determines if the report will print in BAAN or Excel based on
the option buttons on the application worksheet. This subroutine will start the
print DLL in BAAN and send a variable to print in BAAN or Excel. If the
printing is performed in Excel, a repetitive routine is run in Excel to get all the
error records from BAAN. PrinterrorinExcel is run after the record has been
returned from BAAN with the error information. After all the error records have
been retrieved, Printtotals is run.
Excel parameters
None
Subroutines called
SendtoBaan, PrinterrorinExcel, Printtotals
Functions called
Getarg, Getlastarg

Errorsheetsub
Description
This subroutine creates information on the ERROR worksheet. If the ERROR
worksheet does not exist, it will be created. In addition, heading information is
formatted.
Excel parameters
None
Subroutines called
None
Functions called
None

PrinterrorinExcel
Description
This subroutine displays error information. The error information is obtained
from BAAN as the last argument. Use the getarg function to retrieve the field
name, field value, field row, field column, and error message.
Excel parameters
None

OLE with BAAN ERP User Manual

5-2
 Technical information

Subroutines called
None
Functions called
Getarg

Printtotals
Description
This subroutine displays read, processed, and rejected BAAN records. This
subroutine also displays sent, processed, and rejected Excel records. Use the
getarg function to retrieve the read, processed, and rejected BAAN records. The
read, processed, and rejected Excel records are variables that are local to Excel.
Excel parameters
None
Subroutines called
None
Functions called
None

Processallrecords
Description
This subroutine is where the records are assembled according to the mapping
from the InputSpecs worksheets with the Column, Rec Sequence, and Type. The
cell value, column name (letter), and name range are combined with a pipe [ | ]
delimiter into one field.
For example:
DLLFNAME(1996|C|FINBUDYEAR)
An array is used to store the information, which creates the record sent to BAAN.
The two arrays used in this processing have been created in the ValidateRanges
subroutine. The first array is used to build the fixed part and the variable part of
the record. If there are entries in the second array, those will be processed as
well. Once the array has been filled, the record is created and sent to BAAN. See
section ValidateRanges for additional information about arrays.
Excel parameters
None
Subroutines called
CreateRecord, SendtoBaan

OLE with BAAN ERP User Manual

5-3
Technical information

Functions called
Colnumtoletter

CreateRecord
Description
This subroutine formats the record from the array. The format of the record is
DLLFUNCTION(FLD1VALUE|FLD1COLUMNNO|FLD1NAME).
Excel parameters
None
Subroutines called
None
Functions called
None
ConnectBaan
Description
This subroutine establishes the connection to BAAN. Baan4.Application is the
name that must be used because it is used in the BW registry file.
Excel parameters
None
Subroutines called
None
Functions called
None

DisconnectBaan
Description
This subroutine disconnects the BAAN application.
Excel parameters
None
Subroutines called
None
Functions called
None

OLE with BAAN ERP User Manual

5-4
 Technical information

SendtoBaan
Description
This subroutine uploads records to BAAN. The dllname and the dllfunction,
which contains the parameters (see CreateRecord for the format), are sent to
BAAN. Return error codes are checked. If the return error code is normal, the
records read counter is incremented by 1. A return value is also checked for true
or false. True means the record was successfully processed whereas false means
that an error was found in the record.
Excel parameters
None
Subroutines called
None
Functions called
None

ValidateRanges
Description
This subroutine validates the InputSpecs worksheet and the name ranges from
the application worksheets and builds the arrays necessary for Processallrecords.
If there is a validation error, a message listing the error is sent to the user.
Two arrays are created. One array is for the field types: empty and multiple
occurrences (see section 4.2, InputSpecs worksheet). This array contains the
name ranges and record sequence information from the InputSpecs worksheet.
The index for this array is column name (letter).
The second array is for additional rows (type OR) that are needed for the records.
This array contains the worksheet name, name ranges, column, row, and record
sequence from the InputSpecs worksheet. There is no specific index because the
array is incremented by 1. The array includes a variable that contains the number
of entries that are based on the number of occurrences.
Excel parameters
Dllname, Dllfunction
Subroutines called
Checkinputspec
Functions called
None

OLE with BAAN ERP User Manual

5-5
Technical information

Checkinputspec
Description
This subroutine validates the InputSpecs worksheet. See section 4.4, Input
specification worksheet, for the parameters and valid values for InputSpecs. A
message will appear if an error is detected.
NOTE: Errors must be corrected before they can be processed again.
Excel parameters
None
Subroutines called
None
Functions called
None

BuildWorksheet
Description
This subroutine will first validate all the fields on the InputSpecs worksheet by
running Checkinputspec. If all the fields are valid, this subroutine will place the
names from Name Range into the appropriate worksheets and create the Name
Ranges for each column/row. In addition, this subroutine will format the multiple
occurring fields by appending a number incremented by 1 at the end. If dates
must be used, use the start value, fill date, increment, and format date. Use the
fill series with the date option in Excel to complete this process.
Excel parameters
Worksheet name
Subroutines called
None
Functions called
Colnumtoletter

OLE with BAAN ERP User Manual

5-6
 Technical information

Baan.xls worksheet functions

Getarg
Description
This subroutine will get specific data from within a field delimited by a pipe [ | ].
This subroutine is used to get information returned by BAAN when the ERROR
worksheet is being created. This information is stored at the end of an array.
Field name is the variable that contains all the fields. Field number is the
sequential position in the variable that needs to be extracted. The content of the
field is returned by the function.
Excel parameters
Field name, Field number

Getlastarg
Description
This subroutine will receive the last field of a variable. This subroutine is
specifically used for the returned information from BAAN for error reporting in
Excel. The content of the field is returned by the function.
Excel parameters
Field name

CvtString
Description
This subroutine will place quotes around a string field.
Excel parameters
String field

Colnumtoletter
Description
This subroutine will convert a column number into a column letter.
Excel parameters
A numeric field

OLE with BAAN ERP User Manual

5-7
Technical information

OLE with BAAN ERP User Manual

5-8