Macro to re-format Excel BOM output from PCB or Schematic Design Software
Posted by Tim on May 02, 2001 6:15 PM
Most PCB or Schematic Design Software on the market today has the ability to output a Bill Of Material (BOM) in an Excel format. Problem is that re-formatting is required to consolidate component reference designators by Part Number and adding the Qty, is a necessary, but tedious process. Can anyone share a macro that they created to simplify this process?
Posted by Kevin James on May 02, 2001 7:03 PM
2nd fiddle
Tim,
If you don't receive a viable response, I would be interested in seeing what I can do for you.
I am unfamiliar with the industry you reference and I also am NOT a VBA programmer. I am very good with concat formulas which sounds like it might do the job.
If you are interested, please email me a sample of 10 records in an Excel sheet and indicate how the raw data is to be manipulated.
Kevin
Posted by Mark W. on May 02, 2001 7:24 PM
Indented list or single-level explosion?
Posted by Dave Hawley on May 02, 2001 11:04 PM
Hi Tim
I am not familiar with the type of re-formatting you require, but below is a small macro I wrote for a programmer in England that wanted to convert imported numbers from :
1 10 100 1000
TO
A0001 A0010 A0100 A1000
Let me know if it's close and I can modify it for you. This particular macro requires the imported numbers to reside in Column A and Column B to be empty.
Sub NumberConversion()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''
'Converts all numbers below 10000 to 5 characters.
''''''''''''''''''''''''''''''''''''''''''
Dim rConvertRange1 As Range
Set rConvertRange1 = _
Range("B1", Range("B" & Range("A65536").End(xlUp).Row))
rConvertRange1.FormulaR1C1 = _
"=IF(RC[-1]<9999,""A""&REPT(0,4-LEN(RC[-1]))&RC[-1],RC[-1])"
rConvertRange1.Offset(0, -1) = rConvertRange1.Value
rConvertRange1.Clear
Set rConvertRange1 = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Tim on May 03, 2001 9:37 AM
Specifically, take the output
From:
C1, 10395
C35, 10395
C4, 10395
RE33, 11079
RE4, 11079
RE5, 11079
To:
C1, C4, C35 10395 3pcs
RE4, RE5, RE33, 11079 3pcs
I hope this answers your queston?
Tim
Posted by Mark W. on May 03, 2001 2:48 PM
Are there always groups of 3 or is this a coincidence?
Looks like you want to sort by the left-most
alphabetic characters of the 1st column, and the
the right-most numeric, right?
Posted by Tim on May 04, 2001 10:18 AM
Nope, this is just a conincedence. But there will always be only alpha characters in front of only numeric ones. The number of alpha's, and the number of numerics following the alpha's, vary.
Furthermore, C1, C4, C35 is in one cell.
Thanks for your help. Are there always groups of 3 or is this a coincidence?
