As many readers may already know, when I’m not featuring your projects or working on the mooltipass I try to make simple things that may be useful to electronics enthusiasts. My latest creation is a simple bill of materials generation tool, which can also do simple stock management. Unfortunately for Linux users, this utility is made using Visual Basic functions in an Excel file.
It works fairly simply: just enter your schematics’ components references in the excel sheet, along with the corresponding Digikey webpage address. Click on the “fetch” button and the script will automatically get all your component characteristics from the internet and tell you the component costs depending on the number of prototypes you want to make. Then click the “sort BoM” button and your BoM will automatically be sorted by component type and value. Another functionality allows you to check that all the components present in your BoM are also present on the (very simple) Kicad generated one. Finally, using another Excel sheet containing your current stock, the Bill of Materials will let you know if you have enough components for the assembly stage. A video of the tool in action is embedded after the break, and you can download the BoM template here (.XLSM file) and the corresponding stock file there (.XLSM file).
He also uses this for the Easy-Phi project. It is OK in its current state and shows off some really skilled excel scripting and stuff
you overestimate me ;)
VB Functions – so no OpenOffice or LibreOffice eh?
yeah :/ I should definitely port it to one of the two…
Has anyone tried using googledocs for something like this? I was having a poke around recently and noticed all the usual excel functions have been ported in addition to a fair bit of scripting capability for the whole googledocs suite. Could potentially be useful for projects like this…
LibreOffice has (marginal) support for VBA macros… have you tried to run your macros under LibreOffice? (Or are you relying on a windows-specific object to reach out to the internet?)
I haven’t tried libreoffice yet, and unfortunately this macro uses an internet explorer object to fetch the data from the digikey website
Thank you Mathieu
my pleasure!
Frell,
That’s awesome!
Also… in case your internet connection is not good, uncomment the “‘ Application.Wait DateAdd(“s”, 2, Now)” in module1
This looks great. The VB code should be possible to port to shell script, Python, or whatever. That script could easily input a CSV file of components and output a complete CSV file. OpenOffice uses XML, I think, so someone who knows that format could build a parser and generator for it. But CSV is easy and universal.
I too know VBA really well across a number of Microsoft Office apps. And yeah, I can see this would be easy to webify with Python or PHP.
Altium Designer has all this built into it. One of its many features that make it light years better than its “competition”.
*light years more expensive
Sure, but it’s also $6K USD a seat…
explains why I’ve never run across it… sigh.
kicad has a bom tool too.
a very simple one indeed
It wont work why?
Assuming you have a version of excel that will run xlsm macro files, you need to put the file in a folder and then go into options and declare that folder a trusted zone. At that point it should run.
Very keen to try this BOM method for my own work.
I have a little trouble running it at the moment: new install of Office 2010, Windows XP virtual machine.
I have set the folder where the BOM template file is stored as a trusted location within excel, using the “trust centre”.
If I enable “IE.Visible = True” I can see the correct pages are being opened by the scripts…
Could there be some settings which by default won’t allow the scripts to work fully?
Any thoughts appreciated..
Do you mean that no data is entered in the corresponding cells?
Exactly. It does everything but insert the data into the cells such as ‘manufacturer’ and ‘digikey stock’ etc..
I have tried everything I can think of. Would appreciate any suggestions as I can see this becoming part of my toolbox for making hardware.
Also it is at all possible to do the same for Mouser? I have a few parts which Digikey simply don’t stock…
Are you sure you haven’t changed anything in the Template? This may happen if you changed the sheet name. Mouser support hasn’t been implemented yet.
Maybe it has something to do with Windows and Office versions.
I am running win7 32bit with office 2007.
The only thing that seems to function for me is opening the stock file.
Does one need an account on Digikey?
I haven’t changed the file name, but I have made some minor changes to the layout and added more rows etc. So I tried again with a freshly downloaded file, and no difference.
Also the check against KiCAD generated BOM doesn’t work either.
I am sure we can work this out, please persevere with me – I would get such great use out of this template!
Check this for Mouser support too : http://vpapanik.blogspot.gr/2013/12/for-better-bom-in-excel.html
“Unfortunately for Linux users, this utility is made using Visual Basic functions in an Excel file.”
Unfortunately for everyone, really. Open source is not just about open source, it’s about open data formats and avoiding vendor lock-in.
Here’s what we do at work:
Each component placed on a schematic has the following fields (attributes) added to it:
PCB Footprint (we use the IPC naming convention and PCB Libraries’ PCB Library Expert to generate them)
Manufacturer
Manufacturer Part
Distributor
Distributor Part
It’s not as bad as it seems, because once you create a schematic part, you have all that information for the next time you use it. We also have some parts with PCB Footprint already filled in (R’s and C’s for example)
Draw your schematic (we use OrCAD), and as you draw, enter the information into the above fields for each part you use.
When your schematic is complete, run the net lister (uses the PCB Footprint field) and the BOM generator (output is an Excel sheet with the usual information, extracted from the fields you added to the parts). I know some hobby schematic capture systems don’t generate a netlist, and use a single data structure for both Schematic and PCB symbols, but every commercial schematic capture system I know of allows the generation of a netlist and BOM from information stored with the schematic symbols.
It’s really a lot more work to generate a BOM after the fact, and if the part information isn’t integral to the schematic symbols, you run the risk of having disagreements between your BOM and your schematic. And, as I pointed out earlier, once you create a schematic symbol with all the field data, you can save it and never have to enter the data again.
Boy are you optimistic, and lucky.
I was working as local outsourcing for UTC/Pratt & Whitney. They hired the company I worked for to do the “grunt” work so the engineers could be paid to do real work.
Anyways, I forget the software they were using, but it was always just the component. Because the purchasing was always trying to find the cheapest parts, or faster supplier, or whatever was the driving factor that week. So us grunts were always going back and rebuilding the BOMs over and over and over again. Mind numbing I tell ya.
seems like a big change from prior decades… were the product(s) non-mission critical, or should I start being concerned about UTC/P&W’s product line in general?
I couldn’t comment on mission critical or not, my experience isn’t in Aerospace directly. I wouldn’t know a sprocket from a widget. My comment was more about the workflow.
For smaller companies, it might not matter as much. But when its an “hydra” of many heads of driving factors things might not be as simple.
And realize, this isn’t just happening in aerospace. Local outsourcing is hitting alot of the larger companies these days. Automotive, power, aerospace, finance and more.
This is really clever. Well done, [Mathieu Stephan]!
How about adding a feature to get the Digikey hyperlink based on the Part Number instead of having to add the Digikey hyperlink for each part manually?
Hi all,
I just saw this application and it was an “Aha moment!” of the kind that makes you say I hope I am not too late to the party!
II downloaded this Bill of Materials project and thought maybe it will work for me, it does to open a browser and go to the specific part or component – the first one at least- then it just goes to the error line and stops there.
But it fails at this line:
Set objCollection = IE.document.getElementsByTagName(“table”)
and goes to this line:
handleCancel:
If Err = 18 Then
I am using Office 365, I tried to make it work but I am new to vba and programming in general. I am learning though and I am trying to learn programming, specially VBA, as much as possible on my own.
Any help is appreciated.
Thanks