For a better BOM in Excel

NOW OPEN SOURCE : Download (Delphi XE5)

I suppose that the use of spreadsheets like Microsoft Excel for creating a Bill of Materials (BOM) for small electronics projects is widespread among hobbyists, small maker companies and generally when there is no purpose, time or (big) money to upgrade to an elaborated BOM Management System.

Of course, there are various drawbacks when using a simple spreadsheet to keep track of quantities, descriptions and, mainly, stock availability and price for each component that frequently change from the distributor side. It's obvious that these data are crucial for optimizing a design in terms of performance vs. manufacturing cost. However, keeping an 'online' status of a complete BOM using a spreadsheet by hand, i.e.

● Keeping hyperlinks to part pages

● Looking at part pages for availability

● Getting price tags for various ranges (1, 10, 100 etc.)

● Calculating total project cost for various PCB quantities

 is a real pain, especially when you decide to replace a part !

After a long search I could not possibly find out an easy-to-learn and free (or at least for a reasonable price) tool for making my life easier. So, I decided to create my own, based on the online service provided by findchips.com.

You can download the tool from here (32 and 64-bit DLLs, according to your Office edition) :

GetComponent v1.2.1 x32 

or

GetComponent v1.2.1 x64 

It is easy to install and use :

1. Copy the .dll file to the same directory of your BOM .xls file. 

2. Open your bill-of-materials .xls (or .xlsm) file and press Alt-F11

3. Insert a new code module (Insert → Module) and paste the following lines in the whitespace:

#If Win64 Then
    Declare PtrSafe Function GetComponentDLL Lib "GetComponent_1.2_x64.dll" (ByVal Part As Variant, ByVal Distributor As Variant) As Variant
#Else
    Declare Function GetComponentDLL Lib "GetComponent_1.2_x32.dll" (ByVal Part As Variant, ByVal Distributor As Variant) As Variant
#End If

Private Function GetComponent(ByVal Part As Variant, ByVal Distributor As Variant) As Variant
    ChDir (ThisWorkbook.Path)
    GetComponent = GetComponentDLL(Part, Distributor)
End Function

click Save !

4. Prepare two cells, one with the exact part name and one with the distributor name. Most major distributors are supported :

Mouser
Digi-Key
Farnell
Newark
element14
Future Electronics
Arrow
Allied
RS Components

For instance, write the part name in cell A1 and 'Mouser' (without quotes) in cell A2.

5. Select 15 consecutive cells in a column or a row.

6. Press F2 and in the function line write :

=GetComponent(A1;A2)

and press Ctrl-Shift-Enter (yes, it's an array formula). Wait for a few seconds.

If the exact part name and the manufacturer exists, the relevant online information will show in these 15 selected cells, specifically :

Distributor Link

Distributor Part #

Description

Stock

Qty 1

Price 1

Qty 2

Price 2

Qty 3

Price 3

Qty 4

Price 4

Qty 5

Price 5 Currency

Isn't that great ?
Now you have all basic information available, without a single click to supplier pages !

Trouble ? You can download and play with a Demo Excel sheet !

You may notice that obtaining online data is somehow a slow process. So, it is highly advisable to switch to manual calculation in Excel, and recalculate with F9 every time it is actually needed.

Any ideas and suggestions are greatly welcome in the comments.


UPDATE #1 (1/3/2014) : New version v1.1 with bug fixes and support for all major distributors.

UPDATE #2 (15/8/2014) : Excel 64-bit support

UPDATE #3 (29/4/2016) : Unified demo sheet (x32/x64) & status messagebox 

UPDATE #4 (8/11/2016) : Bugs fixed (v1.2.1)