Entering changed values from MS Excel worksheet

The Woodwork for Inventor add-on allows the user to replace the values contained in the descriptions of colours, materials and purchased components with own values. This is achieved by entering values in cells in front of each item presented in the replacement dialogue box. However, such a renaming method is not convenient and can result in errors when handling large amounts of materials or if there is a need to enter material names from a different system. Therefore, the Woodwork for Inventor additionally allows the user to enter the replacement values by selecting them from the table which is created by using the data contained in the MS Excel worksheet. Each configuration can be linked to different MS Excel files

To obtain replacement item data, material/colour replacement configuration has to be linked to MS Excel worksheet of your choice. To do this, you will first have to open the item replacement window. This is achieved by clicking on MS Excel file icon in any cell.

Call Excel replacment

A dialogue box is opened. You can use it to make item replacements by selecting the desired replacement item from the table. If the configuration has no MS Excel source, the table will be empty.

Empty Material replacement data

▪	Setup - allows linking material replacement configuration to MS Excel worksheet.

Linking for materials is done when the opening icon is clicked when the cursor is placed in the columns in the Material replace data section.

Setup Data Source file for replacement

▪

Data Source Files: a browser panels displays all material types in a tree structure and the specific sources that will be used for the specific materials when completing replacement item tables. Each material type can have its own replacement source. On the other hand, you can specify one source for all material types. This can be done by placing the cursor on a higher branch of the tree structure in the browser.

▪	Select Data Source File: selects MS Excel data file that will be used as a source for the item selected in Data Source Files browser.

▪	Sheet of Data Source: selects the worksheet of MS Excel file specified as Data Source File. The table below shows how columns from MS Excel worksheet can be linked to the fields in the replacement item table. The following three fields are given in the initial linking stage:

✓

Material Image: this field allows linking MS Excel worksheet column containing file paths to images that help to better illustrate the content of the replaced item. These images have to be stored in the same directory, in which the file is stored. Or images can be stored in a separately created subdirectory located in the same directory as MS Excel source file. There is no requirement to link this field.

✓	Material Code: this field allows linking MS Excel worksheet column containing material codes. This field must be linked.

✓	Material Name: this field allows linking MS Excel worksheet column containing material names. This field must also be linked.

The user can create their own fields that facilitate navigation or are used as additional fields describing materials. For example, a producer field or a field defining fire resistance of the material, or a field indicating that the material is in stock, etc. If necessary, these additional fields can be exported to BOMs. To this end, the appropriate keywords can be used in BOMs. For example, if you want to export Producer characteristic, enter Material.AddedProperty.Producer keyword in the BOM. Click here for more information.

Linking for colours is done when the opening icon is clicked when the cursor is placed in the columns in the Colour replace data section.

Colour replacements are linked to configurations in the same way as materials; only material types for which colour replacements can be specified differ.

Color replacment Setup

Colour replacement sources can only be specified for the following material types:

▪	Laminated Board

▪

Desktop

▪

Veneer

▪

Edge Band

▪

Paint

Replacement codes and names can also be entered for purchased components (Hardware). In this case, the difference from material and colour replacement is that only one MS Excel worksheet can be specified as the source of replacement items.

Excel relating in Hardware replacemt

Important! In order to replace both the material and colour simultaneously (regardless of whether the replacement is done from the material or colour columns) from the same worksheet, the same MS Excel worksheet must be linked for both the material and the colour. In such case, replacement of colour automatically triggers the replacement of material and vice versa.

In order to open the item replacement window, click the icon with the cursor.

Call Excel replacment

Data replacement dialogue box will open.

Excel Replacment data view

1.	Shows which MS Excel file is used as the data source for replacement values.

2.	Shows which MS Excel worksheet contains the replacement data.

3.	Search (filter) window.

4.	Selection window.

5.	Opens replacement data source setting.

If a replacement configuration has just been created and has not yet been linked to the MS Excel data source, the windows representing the data will be empty. To see the content, click the Setup button to go to the source setting (selection).

Based on the search phrase entered in the search (filter) window, the items shown in the selection window will be selected and classified. You may enter several criteria in a single phrase, for example, the phrase Oak Bolderaja will mean that all items in any field containing Oak and having Bolderaja line fragments will be searched. A space character will be treated as a separator between phrases. If a search phrase contains a space character, it should be placed in quotation marks, e.g. "Milky Oak". This will help to narrow down the search.

The selection of item for replacement is performed by double-clicking the right mouse button after placing the cursor on a selected row.

If the replacement of materials and colours was linked to the same MS Excel worksheet, the replacement form will show the values of replaced fields for both the material and the colour.

Data in the MS Excel file must start from the A1 cell. The number of columns in the file is unlimited.

If one of the file columns displays image names illustrating a row item, and this column is linked to the image (Image) column, then by selecting a replacement item in the replacement window, this image will be displayed in a separate designated column. The image will also be used for appearance replacement. Images must be placed in the same catalogue that contains the MS Excel file, or in deeper catalogues. If images are in a deeper catalogue, the image name must have the name of that catalogue. For example:

Excel Material replacement example

Pfleiderer\Akacija Cinamon R38004_R52052.jpg means that looking from the MS Excel file perspective, the image is in the lower Pfleideer catalogue. The images can be used for changing the model appearance.

As shown in the material above, Woodwork for Inventor provides a wide range of possibilities to link replacement data to the material data stored in MS Excel. We will provide some recommendations on how to organize data.

✓

First recommendation. Use separate MS Excel worksheets for each material type. For example, use one worksheet for Board materials, another worksheet for Laminated Board, a third worksheet for Desktops, etc. Link these worksheets to your material type separately. This way, when opening the replacement table, the worksheet containing the information required for the given type of material will be opened. This helps narrow down the search.

✓

Second recommendation for materials that have information on both the material itself and the colour. There are two options. Link separate worksheets for the material and the colour. This is useful when calculating only the prime cost of a project involving a laminated board, the price of which depends on the supplier and the dimensions of the material rather than on the colour (see figure below).

Laminated board Producer dependent Price List

MS Excel colors

However, it is not suitable in some cases, where the material code has a different material code in the BOM. This is usually the situation when data from ERP systems are used. Linking the material worksheet is enough in this case. If the user needs to know the colour code linked with a particular material, the material and colour information should be kept in the same worksheet and changes for such type of material should be linked in the same table.

Material and colour information in the same space

If data links are even more complicated than it is possible to represent by MS Excel worksheets or the user needs to provide data for replacement directly from an external database, the user can develop his/her own application and provide the data directly in the material replacement dialogue box.