Macro for modifying SolidWorks BOM for MRPeasy BOM import
Purpose: This macro will help you make your SolidWorks exported Bill of Materials suitable for MRPeasy. It will show the parent assembly article code of every part on its line. Otherwise, MRPeasy import wouldn't know which BOM to place this part in.
Steps for importing a Solidworks BOM via CSV:
- Export the indented BOM from SolidWorks to Excel.
- The hierarchy needs to be represented by decimal numbers. (e.g. A direct part of the main assembly is 7, and sub-assembly component items are 7.1, 7.2, 7.3, 7.3.1, 7.3.2, etc.).
- Modify it with the macro code (see below).
- Each line must have a direct parent part number to which the specific child part number refers.
- Save it as a CSV file.
- If some parts do not exist as items in MRPeasy, create these items first.
- Before the BOM can be imported, all part numbers, including sub-assemblies, need to be created or imported into MRPeasy.
- Read: How to import items?
- Import the BOM structure into MRPeasy at Production planning -> Bills of materials -> Import from CSV.
Formatting of the spreadsheet for this macro:
- No headers.
- Each line represents one part.
- The first column (A) shows the code of the part's parent assembly.
- The second column (B) shows the hierarchy of parts (1, 2, 3, 3.1, 3.2, 3.3, 4, etc.).
- The third column (C) shows the part number.
- The fourth column (D) shows the quantity of the part.
Usage:
- Into cell A1, enter the article number of the final assembly (the article number this BOM refers to)
- Into cell A2, copy the macro formula (see below).
- Drag-copy A2 until the end of parts.
This way, Excel will modify the macro according to each line and fill the cell with the code for the part's direct parent item. - If there are BOMs (of sub-assemblies), which appear several times in different branches, then such duplicates must be removed manually.
- Export it as CSV and import it to MRPeasy at Production planning -> Bills of materials -> Import from CSV.
Macro code:
Which macro will work depends on the configuration of your PC and Excel.
If you use full stop as a decimal separator (i.e., sub-assembly code is "7.2"):
Comma delimited (commas used inside Excel formulas):
=IFERROR(IF(IFERROR(LEFT(B2,FIND("^^",SUBSTITUTE(B2,".","^^",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))-1), "error1") = "error1",A$1,TRIM(VLOOKUP(LEFT(B2,FIND("^^",SUBSTITUTE(B2,".","^^",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))-1),B:C, 2, FALSE))), TRIM(VLOOKUP(VALUE(LEFT(B2,FIND("^^",SUBSTITUTE(B2,".","^^",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))-1)),B:C, 2, FALSE)))
or
Semicolon delimited (semicolons used inside Excel formulas):
=IFERROR(IF(IFERROR(LEFT(B2;FIND("^^";SUBSTITUTE(B2;".";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;".";""))))-1); "error1") = "error1";A$1;TRIM(VLOOKUP(LEFT(B2;FIND("^^";SUBSTITUTE(B2;".";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;".";""))))-1);B:C; 2; FALSE))); TRIM(VLOOKUP(VALUE(LEFT(B2;FIND("^^";SUBSTITUTE(B2;".";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;".";""))))-1));B:C; 2; FALSE)))
If you use a comma as a decimal separator (i.e., sub-assembly code is "7,2"):
=IFERROR(IF(IFERROR(LEFT(B2;FIND("^^";SUBSTITUTE(B2;",";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;",";""))))-1); "error1") = "error1";A$1;TRIM(VLOOKUP(LEFT(B2;FIND("^^";SUBSTITUTE(B2;",";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;",";""))))-1);B:C; 2; FALSE))); TRIM(VLOOKUP(VALUE(LEFT(B2;FIND("^^";SUBSTITUTE(B2;",";"^^";LEN(B2)-LEN(SUBSTITUTE(B2;",";""))))-1));B:C; 2; FALSE)))
Example
Step 1. Export a BOM from Solidworks.
It will have the following format:
| Level | Part # | Quantity |
| 1 | Part #1 | 1 |
| 2 | Assembly #2 | 3 |
| 2.1 | Part #2 | 5 |
| 3 | Part #3 | 7 |
Step 2. Modify the Solidworks BOM in Excel.
For MRPeasy to be able to import a multi-level BOM structure, the BOM will need to be modified so that the first column will indicate each part's and assemblies' direct parent part number. It must be in the following format.
| Parent part # | Level | Part # | Quantity |
| Assembly #1 | 1 | Part #1 | 1 |
| Assembly #1 | 2 | Assembly #2 | 3 |
| Assembly #2 | 2.1 | Part #2 | 5 |
| Assembly #1 | 3 | Part #3 | 7 |
Step 2.1. Delete the header row.
Step 2.2. Use the macro.
| Write the main assembly part # here | 1 | Part #1 | 1 |
| Copy the macro to here | 2 | Assembly #2 | 3 |
| Drag-copy macro from above | 2.1 | Part #2 | 5 |
| Drag-copy macro from above | 3 | Part #3 | 7 |
Step 3. Save the file in CSV format.
Step 4. Import the CSV file to MRPeasy.
Important notes:
- Choose "Do not import" for the "Level" column.
- Choose the "Product nr" column for the "Parent part #" column that was filled with the macro code.
- Choose the "Part nr" column for the "Part #" column.
- Choose the "Quantity" column for the "Quantity" column.
- Items with all the part numbers must be created beforehand; otherwise, the import will fail.
- Duplicate subassemblies must be manually removed from the file; otherwise, the subassembly's BOM will be corrupted.