ROB-EX has an option for importing and exporting master routes and master BOM’s from Excel. This is provided as scripts/macros you install to your <sch_client_dir>\custom folder, and execute from macros. From ROB-EX v6.4, the scripts are included as part of the client installation in <sch_client_dir>\data\scripts\excel\masterRouteImportExport

This is a great option for setting up a plan for the first time, including creating a mockup plan for demo purposes. Note you may also use this option to import a bunch of resources and resource groups. You may watch this video to learn how to install and use this option.

Installation

Copy the content of the masterRouteImportExport/custom folder to your <sch_client_dir>\custom folding (excluding the *.sql file). Restart your ROB-EX client and answer yes to import the two new macros. One macro for import and one macro for export.

Example Excel template

You may create a template Excel file by opening an existing ROB-EX plan, next run the “Master Route Export” macro. Specify e.g. “Robex_excel_routes.xlsx” as the file name.

Alternatively an example Excel template is also available for download from this link. The columns of this template has additional help notes regarding the meaning of the different codes. The example data in the Excel template, models the following two master routes Item A and Item B

Usage for import

Regarding the Routes tab

  1. Each row in the sheet is an operation – either a primary or a sub-operation. Repeat the “Route Name”/“Route alternative name” for each operation on same route.
  2. The combination of “Route name” and “Route alternative name” should be unique among all master routes
  3. If a resource does not exist it will be created – also the group it belongs to is created if it does not exist. New resources will be assigned the system default general calendar (Edit->Calendar->Properties->“Use as default calendar”). So, this is also a great tool for importing resource master data.
  4. Use the “Opr no.”, “Depends on” and the “Binding rule” columns to create advanced constraint links

    Note: the values in “Depends on” can be a comma separated list (e.g. “20,30”) to model many-to-one relationships. Multiple different operations may depend on the same operation no, to model one-to-many.

Binding rule meanings are (same values allowed in other interfaces like XML):

  • 1=SS=Start/Start=StartStart
  • 2=FF=Finish/Finish=FinishFinish
  • 3=FS=Finish/Start=FinishStart
  • 4=SF=Start/Finish=StartFinish
  • 5=SSE=Start/Setup=StartSetup
  • 6=SSW=Start/Switchover=StartSwitchover
  • 7=SES=Setup/Start=SetupStart
  • 8=SESE=Setup/Setup=SetupSetup
  • 9=SESW=Setup/Switchover=SetupSwitchover
  • 10=SEF=Setup/Finish=SetupFinish
  • 11=SWS=Switchover/Start=SwitchoverStart
  • 12=SWSE=Switchover/Setup=SwitchoverSetup
  • 13=SWSW=Switchover/Switchover=SwitchoverSwitchover
  • 14=SWF=Switchover/Finish=SwitchoverFinish
  • 15=FSE=Finish/Setup=FinishSetup
  • 16=FSW=Finish/Switchover=FinishSwitchover

Regarding the BOM tab

Each row is a single BOM line – repeat the “Route Name”/“Route alternative name” for each BOM line on same BOM.

  1. The material is the produced material (will be assigned to the operation specified in column “Material Opr no.”. Or if this is missing, to the last operation in the route)
  2. and the “Comp Material Number” is the consumed material (will be consumed on the operation specified in column “BomLine Opr no.”. Or if this is missing the first operation of the route).

Any material used which does not exist is automatically created (this is also a great tool for importing material master data).

  1. The rate consumed is specified using the columns “BomLineBaseQuantity” and “BomLineQuantity”
  2. In the example above the BOM line consumes 20 pcs for each 100 produced. So in normal ROB-EX rate term that is 20/100 = 0.2

Material type:

  • 0: raw material
  • 1: resource tool
  • 2: semi finished
  • 3: finished good

BomLineAccessMethod:

  • 200 (Front), 201 (End), 202 (Linear), 203 (Front_Incl_Setup), 204 (End_Incl_Switchover), 205 (Linear_Incl_Setup_And_Switchover), 206 (Linear_Over_Workload), 207 (Linear_Over_Fillup), 210 (Dependency_Front), 211 (Dependency_End), 220 (Next_Operation_Start), 221 (Next_Operation_End), 222 (Offset_Front), 223 (Offset_Front_Incl_Setup), 224 (Offset_End), 225 (Offset_End_Incl_Switchover)

Recursively import excel files in a directory

At the top of the script you have this option “importRecursively”:

// set true to import multiple files using a pattern
public static boolean importRecursively = false;

If you set this to “true” then all files mathing the “fileRegexpFilter” will be imported. After import the file is moved to the “processedDir” folder. It is important that you adjust the rootDir and processedDir to point to something valid for your installation.

if (GlobalVars.importRecursively) {
   // get parent path - and import all xlsx file in that directory
   def fileRegexpFilter =  ~/(?i).*master.*.xlsx$/
   // edit root dir here
   def rootDir = "c:/users/jensb/rbx62/input" 
   // edit processed dir here (files are moved to this location after being processed)
   def processedDir = "c:/users/jensb/rbx62/input/processed" 

By default when importRecursively = false it will ask the user for the file to import.

Note that the “importRecursively” only applies to import. Export is always to a single selected file.

Usage for Export

Run the “Master Route Export” macro. You will be asked for the location of the exported excel file.

Note that the exported file can also be imported – i.e. the export and import follow the same syntax. This way it is possible to e.g. export all existing master routes to Excel. Perform a mass change to e.g. the setup in Excel. Finally re-import the master routes. The existing master routes in ROB-EX will now be updated with the new changed setup times

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment