The BOM > The Excel BOM > Customize the Excel BOM

Excel BOM Advanced Scripting

Set the file name for the BOM to be created

The scripting library Excel_BOM.lib contains a function that allows to predetermine the file name for the Excel BOM that will be created.

The function is:

Function ExcelBOM2(uid, par_file, excel_file_name)

and with respect to the previously available function

Function ExcelBOM(uid, par_file)

a parameter has been added to set the file name preemptively.

Retrieve the user selection for the type of BOM

The calling script can obtain the information on the user's selection for the type of BOM.

This simple example shows how:

.VBSCRIPT

Sub Main()
DBWInit(TRUE)
BOMfile = ExcelBOM("","")
DBWMsgBox getValueFromMemory("ExcelBomUserSelection")
End Sub

The function getValueFromMemory allows to set values in memory that remain available for any script until the application (usually the 3D CAD itself) is closed. Inside the new version of the script that creates the BOM the value has been saved into memory and can be retrieved after its execution.

Support for the interception of the event for the Excel cell being filled

When you need to set specific values in the Excel BOM you can define a script that is called for each cell and allows you to set the specific cell value.

How to proceed

Suppose that you decide to name the script used to manage the 'fill cell' event myScript.vbs
Suppose that you want to enable the handler for the model Buy List
Open the file ...\PAR\DBWBOM_Buy List.txt
In the sections OPTIONS add the line CELL_VALUE_HANDLER myScript.vbs as in the following:

OPTIONS

BOM_TYPE BuyList
INDENTATION 3
FILTER T='P'
EXCEL_MODEL DBWEXCELBOM.XLS
MULTILINE ", "
CELL_VALUE_HANDLER myScript.vbs

In the directory ...\LST create an empty vbs named myScript.vbs
In the script define the function cellValue as in the following sample:

function cellValue

cellValue = CStr(value)
End function

If this function exists it is called for each cell to allow you to intercept and se the cell value as they are written into the Excel sheet.

Parameters

The following parameters are made available to the script and are available in the function also if they are not declared:

Worksheet	The current Excel worksheet object as defined in the Excel DOM
Column	The letter indicating the column as in "A"
Row	The integer indicating the row as in "5"
Value	The value that is about to be set to the cell. This value can be changed
columnName	For cell values, the database column it refers to as in "DESCRIPTION" or "BOM_POSITION"
dataType	The type of cell that is being set, Can be: "HEADER" for a header label or value "COLUMN_HEADER" for the header of a column "VALUE" for a cell value that normally comes from a database field value "NOTE" for a cell comment visible when the mouse moves over the cell
rsDocument	Only in Excel output, contains an ADO recordset with the record from the DOCUMENT table
rsDocumentIsNothing	Boolean, allows to know if the recordset related to the document is available
rsBom	Only in Excel output, contains an ADO recordset with the record concerning the BOM (QTY, LEVEL, BOM_POSITION, IS_LEAF, IS_FAKE, UM)
rsBomIsNothing	Boolean, allows to know if the recordset with BOM data is available
<Field properties>	Only in text output (DBWGetDBWBomAsArray API call) contains the field values as simple variables (i.e. ID, Description...) but only for the columns up to the current, so on its left side in the output

Data available to the script

some data is availble to the script through 2 ADO recordset: rsBOM (field values of the BOM) and rsDocument (field values of the DOCUMENT table);
rsBOM fields are the followings:

Field name	Field type
UID	int
LEVEL	int
QTY	adVarChar(40)
TOTAL_QTY	adVarChar(40)
IS_LEAF	adBoolean
UM	adVarChar(50)
T	adVarChar(1)
PARENT	int
DOCUMENT_ID	adVarChar(255)
IS_FAKE	adBoolean
DRAWINGS_NUMBER	int
REVISION_NUMBER	adVarChar(40)
MULTIPLIER	int
MAKE_BUY	adVarChar(40)

Return Values

The function will return a string value if the value passed as a parameter needs to be changed. If no value is assigned then the cell is filled with the original value coming from the database.

Example

Here's a simple example of a function that modifies only one column header

Function cellValue

If column = "A" and row = "12" Then
cellValue = "Custom column header"
End if
End Function

Managing installations where multiple positions in Boms are considered normal/frequent

DBWorks allows the users to assign the Bom positions manually also to generic documents, but there are installations where the documents are added in batch or by script and it is not always possible to reorder the positions eventually.

1	Part1
1.1	SubPart
1	Part2
1.1	SubPart2
1.1.3	SubPart3

In these cases the BOMs contains duplicate positions, that lead to confusion and potentially incorrect outputs. To handle these cases you can add the string DISTINGUISH_DUPLICATE_POSITIONS to the BOM parameter files, in the section dedicated to the options:

...

OPTIONS
    INDENTATION    3
    FILTER        LEVEL>0
    BOM_TYPE    INDENTED
    INCLUDE_PART_CHILDREN
    EXCEL_MODEL
    ERROR_ON_DUPLICATE_POSITIONS
    DISTINGUISH_DUPLICATE_POSITIONS
...

If this string is found then a letter is added to the field BOM_POSITION of duplicate positions to avoid confusion:

11	Part1
11.1	SubPart
1b	Part2
1b.1	SubPart2
1b.1.3	SubPart3

Support for PARENT_CHILD / DOCUMENT field override

Suppose you want to fill the value of the field DESCRIPTION with a specific value in a given Parent Child context.

If you define a field in the PARENT_CHILD table named PARENT_CHILD_DESCRIPTION, and its value is not null, then that value will replace and overwrite the value defined in the DOCUMENT table in that specific context.

The same holds true for every DOCUMENT field as long as the naming convention PARENT_CHILD_FIELDNAME is respected ( Please note that the PARENT_CHILD table name can be localized to every language )

The enhancement applies only to indented BOMs.