Macro for modifying SolidWorks BOM to work in MRPEasy batch 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.

Pre-requisites:

  • Learn how to import BOMs to MRPEasy via CSV.
  • BOM from SolidWorks has to be exported 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.).
  • All part numbers, including sub-assemblies, need to be created or imported into MRPEasy before the BOM can be imported.

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)))