User Manual

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 to.

Steps for importing a Solidworks BOM via CSV:

  1. Export the BOM from SolidWorks to Excel.
  2. Modify it with the macro code (see below).
  3. Save it as a CSV file.
  4. If some parts do not exist in MRPeasy, import these parts at Stock -> Items -> Import from CSV.
  5. Import the BOM structure into MRPeasy at Production planning -> Bills of materials -> Import from CSV.

Pre-requisites:

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 main assembly (the article number this BOM refers to)
  • Into cell A2, copy the macro formula (see below).
  • Drag-copy A2 in until the end of parts. 
    This way Excel will modify the macro according to each line and fill the cell with the code of the part's direct parent item.
  • Export as CSV and import 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 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 comma as decimal separator (i.e. sub-assemly 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

A solidworks BOM export will give out the following format:

Level Part # Quantity
1 Part #1 1
2 Assembly #2 3
2.1 Part #2 5
3 Part #3 7

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 assemblie's direct parent part number. As follows:

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

Filling of the first column values can be achieved with the macro code above, by first deleting the header row and then using the macro as follows:

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