SW.tips > Excel Add-in Settings

It is possible to customize the Excel Add-in to fit your needs. The settings are presented as a datagrid where the variable names can be modified and some application settings can be enabled or disabled.

Application

The following settings define the configuration of the add-in itself. These should be setup only once and maintained for consistency.

Note that these settings are shared across all the miniPDM add-ins but not across all the database user as some might want to be able to configure their miniPDM independently of the rest of the user pool.

Database Location

You can either copy-paste the path of the Access database file here or click on “…” on the right side of the cell to browse to the file. To learn more about this setting please check the article about the miniPDM Database.

External Unique Identifier Generator

In the future, it will be possible to use an external program to generate the Unique Identifier to allow more customization. For the moment you can keep the default setting on False. For more information, see the article about the Unique Identifier.

Force Upper Case

This setting can force all the values that can be set in the Component Extended Properties and on the spreadsheet itself, to be converted to upper case for format consistency. Select True in the dropdown menu to activate this option.

Shared Settings

If this setting is set to True, the settings stored in the database will overwrite the one stored in the application. This is an easy way to make sure all the users have the same settings to have consistency.
When set to False, the settings will be only stored in the local application.
Note that a database is required to activate this setting.
If a setting does not exist in the database but exists in the application, it will be written in the database. This is particularly useful when creating the shared settings for the first time before sharing it with others.

Colors

Color Gradient

This setting defines the top color of gradient used in the miniDPM – Tree tab to visualize the rank of a component. The highest rank, or top assembly, will have this color and it will fade towards white the deeper down the tree a component is ranked.

Even / Odd Rows Color

These settings define the color of the even / odd rows in the miniPDM – Computed tab.

Internal / External Color

These settings define the color of the Internal / External components in the miniPDM – Computed tab. To learn more about the difference between an internal and external component take a look at the article about Definition of the Word Component.

Columns

This section defines the column names that the add-in read / writes. The names can contain spaces and can be different than the ones in the SolidWorks Add-in.

It is advised to define once and for all these variables names otherwise a BOM containing old and new components will have empty values in the cells where the variable names don’t exist.

Mandatory Columns

This sub-section defines the column names of the columns that are required to compute a BOM. See the article about the Excel Add-in Features.

Optional Columns

Note that some column names are dependent on other columns and can only be activated if certain conditions are met.

Class

This sub-sub-section defines the Class of a component that is useful for filtering. Values in this column have to be separated by a Separator for the program to read them properly.

Parent / Child values are added automatically by the program. If a component contains one or more components it is a Parent, otherwise it is a Child. It allows to filter assemblies (or modified parts) and parts for example. For more information see the article about Components Modification.

Single / Family values can be used in the Property Tab Builder or in SolidWorks PDM for advanced conditional properties but can be also used as filter in the Excel Add-in. See the article about the Components Families

Internal / External values can be used to color accordingly the text in a component row in the miniPDM – Tree tab to quickly identify which component is Internal or External. See the article about the Definition of the Word Component.

The Separator can be for example ” / “. This include the space before and after “/”.

Description

The Description is the name of a component which is different from a Part Number. The variable name should be “Description” as this variable is used in SolidWorks, PDM and Windows Explorer. For more information see the article about the Description Property.

Distributor

The Distributor is different from the Manufacturer. A manufacturer is the only one to make a component while this very component can be sold by multiple Distributors.
By default, the Manufacturer is also the Distributor unless a Price Conditions is specified with a Distributor name.

Distributor Reference

Likewise, the Distributor Reference is different from the Manufacturer Reference. A distributor can use a different reference to define a component made by a Manufacturer.
By default, the Manufacturer Reference is also the Distributor Reference unless a Price Conditions is specified with a Distributor Reference.
The Manufacturer Reference is commonly called “Part Number” but in some cases it can be confusing. For example when a component is actually an assembly and not a part or if a component is modified (see the article about Modified Components).

File Name

This sub-sub-section defines the name of the column used to display the path to the file that contains the component. See the article about the Definition of the Word Component.

Note that if you have followed the advice to name the files after the Unique Identifier (see the article about the Files Naming), in the case of Components Families or in the case of Virtual Components, the document name and the Unique Identifier can be different.

The Extension setting is used to define the naming convention on the files associated to a component to activate create an hyperlink on the path. The order is important as the program will stop to the first matching occurrence of the list. Each possible file name has to be separated by “;” and include the file extension.

To create an hyperlink to the PDF file of a drawing that would be named after the document with the revision separated by “-” you can enter:
[DOC. NUMBER]-[REVISION].pdf
If you don’t always have a PDF but you would like to open the SolidWorks Drawing instead that does not carry the revision, you can add:
;[DOC. NUMBER].slddrw
Then if you want to open the part file or the assembly file to which belong the component you can add:
;[DOC. NUMBER].sldprt;[DOC. NUMBER].sldasm
In the end the full string would look like this:
[DOC. NUMBER]-[REVISION].pdf;[DOC. NUMBER].slddrw;[DOC. NUMBER].sldprt;[DOC. NUMBER].sldasm

Measure

This setting defines the name of the column that displays the actual value of a measure. This column has to exist in the raw BOM source. See the article about the Components Defined by a Measure.

Note that this column is not present in the miniPDM – Computed tab.

Price

This setting defines the name of the column that displays the Price of one unit (or one pack) of a component if there is a pricing in the database.
The Extended Price column displays that Price multiplied by the total number of identical component (or pack).
For more information, read the article about the Price Conditions.

Procurement

This setting defines the name of the column that displays the Procurement which is the Quantity that actually needs to be sourced to match the desired Quantity taking into account the packing size and potential larger order discount.
The setting that defines the name of the column that displays the Spare is used if you want to add an extra Quantity to have spare components.

In the miniPDM – Computed tab, the Quantity column details the calculation to obtain the total number of required components by sub-assemblies.

If the “=” is replaced by “→”, that means that it is cheaper to by more components than required to benefit of a bulk discount.
If a number is between “( )” that means that the quantity does not come from a sub-assembly but from a Spare quantity.
If there is “x” in the formula that means that the component is sold by a measure and represents the number of time this very measure is used in the sub-assembly.
If the sum is followed by a formula between brackets, it means “number of pack” x “quantity per pack”. The Procurement column reflects the number of pack to order.

Revision

To be able to create an hyperlink to a File Name with a Revision, the column that displays it has to be identified. For more information on where this property value should be filled, see the article about the Component Properties.

Unit

The Unit does not only define a reference for the Measure but is also used by the Price Conditions to define the packing size of a component.
If a component is sold in pack, bags, box, etc… the Unit will display the value of variable named Multipack. Otherwise it will display the value of the variable Single Pack.
The default value if there is no Price Conditions is defined by the variable name Unknown Packing.

Sheet

This section defines the name of the workbook’s tab used by miniPDM. You can put any name you want but make sure it is easy to identify if you have a lot of worksheets in your workbook.