How to Create a Visual Inventory Document using MS-Excel

Have you ever tried to

Not too easy, is it? Everyone uses different nomenclature for inventories, and sometimes it's a challenge to imagine what a part looks like from its description. (Just what is a Technic Connector Block 3 x 6 x 1 & 2/3 Gearbox, anyway?) It's also very easy to mistype Ldraw numbers as you're making an inventory, and hard to proofread without doing the whole thing again. Wouldn't it be nice to have a document with a picture of each piece in a set?

This procedure uses a flat file containing an inventory in the format used by Peeron to create an HTML file containing annotated images of each part in the inventory in the appropriate colour. Parts whose images don't appear in Partsref will show a broken image, but the annotation will remain.

Example of input: 6035_inv.txt

Example of output: 6035_vis.htm

Preparation:

Copy colours.xls into your working directory. Check the colour list to make sure the colours used in your inventories are on the list with the correct colour number. If a colour in your inventory does not appear in this list, the colour number returned will correspond to the next largest value that is less than the colour in your inventory. For example, if your inventory contains "Red/White/Black" the colour will resolve to "Red" as long as "Red" is the closest item above where "Red/White/Black" would fit alphabetically. For this to work, the list has to be in ascending alphabetical order by colour.

The calculation goes much faster if this file is open while you are working, but it doesn't have to be if it's in the same directory.

Step 1:

Bring the inventory into Excel. Tab- and comma-delimited as well as fixed-width text files will import to Excel with no problem; so will HTML files with the inventory in a table.

Step 2:

Make sure the inventory is in the format specified by www.peeron.com/inv, that is:


 
Quantity LDRAW # Bag # Colour Part Description Printing/Notes

OR the format of output by ML-CAD:


 
Step Quantity (Numb.) Color Part (LDraw #) Part Description

The actual column headers don't matter.

If you have extra rows above or columns to the left of these, that's OK. (For example, if I'm adding Ldraw numbers to several inventories at once, I'll put them all in the same file and sort by piece description; and put the set # in column A in order to separate them again.)

Sort the rows in any order you like.

Step 3:

Copy the following formula to the cell immediately to the right of the last column in each row by pressing F2 and then Ctrl-V.  You can't just press Ctrl-V or Edit/Paste without pressing F2 to edit the cell first, because the smartypants program interprets the HTML as formatting!

For peeron.com/inv format:

="<img src="&CHAR(34)&"http://img.lugnet.com/ld/"&VLOOKUP(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-3))),colours.xls!colour_table,2,TRUE)&"/"&LOWER(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-5))))&".gif"&CHAR(34)&"> x "&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-6)))&" = "&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))&" <em>"&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))&"</em>, <strong>"&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-3)))&"</strong><br>"

For ML-Cad output format:

="<img src="&CHAR(34)&"http://img.lugnet.com/ld/"&VLOOKUP(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-3))),colours.xls!colour_table,2,TRUE)&"/"&LOWER(CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-2))))&".gif"&CHAR(34)&"> x "&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-4)))&" = "&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-1))), <strong>"&CELL("contents",INDIRECT(ADDRESS(ROW(),COLUMN()-3)))&"</strong><br>"

I know it’s gross. Trust me. Most of the complexity allows it to avoid using specific cell references. It will resolve to a snippet of HTML including a link to the image at Partsref of the part on that line in the right colour. You can change the HTML, except for the formula that creates the link, any way you like. Don’t forget to use CHAR(34) for double quotes.

Step 4:

Highlight all the cells containing this formula, copy, and paste them to a text file in Notepad or your favourite text editor. Save the file with an HTM or HTML extension under any name you like.

Step 5:

Connect to the Internet. Use your browser to open the file you just saved.