Excel Automation

This feature allows you to generate automatically schematic drawings from data contained in an Excel file. This file must have a specific format to be imported in SOLIDWORKS Electrical.

This feature may be difficult to implement for beginners. To help you along, you can download an example, at the end of this chapter.

 

Compatibility

The Excel Automation feature is only compatible with Excel 2016 and 365, with the 64 bits edition.

Procedure

The Excel Automation feature uses a formatted XLS file containing instructions to create or to use books, folders and drawings. These drawings are automatically generated from specific macros in which you use variables.

1.     The first step is to create the macros you want to use.

2.     Then you have to create the XLS file from the file template.

3.     The last step is to import the XLS file using the Excel Automation feature to generate the drawings.

Macro definition

The XLS Automation feature only uses schematic macros, the other macros are not available and must not be used. These macros are specific and must include these two rules, insertion point and variables.

Insertion point

The macros are automatically inserted in the drawing and must have a specific insertion point. From the Insertion point feature, click the point where you want the macro to be inserted. You will have to enter the coordinates of this point in the XLS file.

Variable definition

The macro contains variables that propagate information in the scheme. These variables will be replaced by texts you will set-up in the XLS file. The variables must be formatted with the "%" character placed before and after the variable. For example %VAR1%, in which "VAR1" is the variable name. The text used in the variable is free but it can be the same in the macro and in the XLS file.

 

You can use variables to replace:

- All objects with a mark (such as components, locations, or drawings): To manage marks (location, function or component) edit the macro from the Macros management and replace the mark by the variable. For example, if the macro contains a contactor, marked K1, replace this mark by the variable %COMPMARK1%. In the same way, using the Location management, you can replace the location mark by a variable, like %LOCMARK1%, and assign this location to a component.

- Manufacturer parts: Concerning the manufacturer part, associate the component to a manufacturer part in which you enter variables in the manufacturer name (for example: %MANUFACTURER1%) and for the manufacturer part reference (For example: %MANUFPART1%).

- Equipotential numbers: To use variables in the equipotential numbers, edit the wire equipotential number and replace it by a variable, like %EQUIP1%.

- Wire style names: To use variables in the wire style name, open the Wire style management. In the wire style properties, replace the wire style name by the variable (For example: %WireStyle1%).

- Translatable data and user data: Variables can be used also on the description, user data or translatable data of any object that has a mark (For example: %TransData_1_ES% for the first translatable data in Spanish).

Excel file definition

Several template Excel files are delivered with SOLIDWORKS Electrical to help you to create your own Excel files. They are stored in the \XlsAutomation\Template folder of the SOLIDWORKS Electrical data.

 

Menu: Import/Export > Excel Automation > Create new Excel file for automation

 

1.     Select in the list the template file you want to use.

2.     Save your file in your personal folder.

 

The Excel file can be from version 97/2003 (.xls), up to 2013 format (.xlsx) or xlsm file format (Excel file with macros). Only the first visible sheet of the file will be processed, others will be ignored. There is no restriction on the sheet name.

 

The Excel file must contain specific instructions to be used to do the Excel Automation. It can have as many rows before the header, as you want. The process reads the first row that has the first string that starts with the "#" character. It is the header line.

This header line must have two kinds of information; field name (#field) and variable name (%variable%).

You can manage several languages in the file header. Insert a column using the #title_lang field to identify the texts describing the different languages.

Field names

To identify a Field name, it must start with the "#" character. Generally, the Field name corresponds to the field name in the database. For example, "#fil_title" corresponds to the mark of the drawing in which the macro will be inserted. Some of these fields are required to obtain a consistent result.

The first field names that you have to manage are corresponding to the macro name and positions.

 

Macro

FIELD NAME	DESCRIPTION	REQUIRED
#mac_name	Name of the macro	Yes
#mac_posx	The X coordinate where the macro will be inserted	Yes
#mac_posy	The Y coordinate where the macro will be inserted	Yes
#mac_insert	If this column is present, no hidden, contains a 0 or is empty, the macro will not be processed.	No

 

About the other fields of database, SOLIDWORKS Electrical only takes on fields for tables of drawing ("fil"), bundles ("bun"), locations ("loc") and functions ("fun").

The only field that is required is #fil_title, which is the title of the drawing where the macro will be inserted. But if you insert any other field like book, location or function, then the tag of the corresponding table is required. For instance, if you insert any location field, then the field #loc_text is required.

 

The supported fields are shown on the next table:

 

Drawings

FIELD NAME	DESCRIPTION	REQUIRED
#fil_filename	Name of the file on the disk	No
#fil_title	The mark of the drawing	Yes
#fil_filetype	The drawing type (*)	No
#fil.tra_0.xx	The description of the drawing, where xx is language code	No
#fil.use_data0	User data of drawing	No

 

(*) Supported values for the file type are:

0: Schematic drawing

1: Line diagram drawing

5: Cover page

9: Cabinet layout drawing

12: Mixed scheme drawing

 

Books

FIELD NAME	DESCRIPTION	REQUIRED
#bun_tag	The mark of the book	Yes (if any book is used)
#bun.tra_0.xx	The description of the book where xx is the language code	No

 

Folder

FIELD NAME	DESCRIPTION	REQUIRED
#fol_name	The mark of the folder	No (if any folder is used)
#fol.tra_0.xx	The description of the folder where xx is the language code	No

 

Locations

FIELD NAME	DESCRIPTION	REQUIRED
#loc_text	The mark of the location	Yes (if any location is used)
#loc_tagpath	Mark path (full mark)	No
#loc_tagroot	Root for mark	No
#loc.tra_0.xx	Description of the location where xx is the language code	No

 

Functions

FIELD NAME	DESCRIPTION	REQUIRED
#fun_text	The mark of the function	Yes (if any function used)
#fun_tagpath	Mark path (full mark)	No
#fun_tagroot	Root for mark	No
#fun.tra_0.xx	Description of the function where xx is the language code	No

 

By default, all the objects created; drawings, books, locations and functions will be created with manual marks. If you want them to be automatically numbered, then you should insert the field xxx_tagmanual or xxx_manual and set it to 0. In this case, the file mark will be generated by SOLIDWORKS Electrical according to the formula defined in electrical project settings.

 

User data and translatable data can be also added as a field, but as they are not directly in the object table, the field definition is a bit different.

It is formatted in the following way for translatable data: #ttt.tra_nn.xx

Where

- ttt is to be replaced by the table prefix (fil, bun, loc, fun).

- tra is for translatable data (do not modify).

- nn is to replace by index of data you need (0 for description). Up to 14 for translatable data.

- .xx is to replace by the language code, usually .l1 (L1) for main electrical project language. Supported language codes are:

- From electrical project languages (L + 1 to 3): "l1", "l2", "l3"

- Standard language code: en, es, fr, it, ru, ko, ja, pt..., zh, zh-tw

 

Example, for the description of the file in the main language of the electrical project: #fil.tra_0.l1

 

For user data, the syntax is similar, but without specification for language: #ttt.use_datann

Where

- ttt is to replace by the table prefix (fil, bun, loc, fun).

- use_data is for user data (do not modify).

- nn is to replace by index of data you need, from 0 to 19 for user data.

Example, for file user data: #fil.use_data0

Variables

After you have specified all the data, you need to say where to insert the macros (drawing, book, location and function) and you must specify the variables, which you want, to be replaced by their values. To do this you can use two methods.

 

The first method consists of writing in the first available column of the header, the tag "#mac_var_name" and on the next one "#mac_var_value", and consecutively, as many pairs of columns as you need. After that, for each row (for each macro you want to insert), you want to write, on the first column, the name of the variable and on the second column, the value of this variable. For example:

#mac_name	#mac_var_name	#mac_var_value	#mac_var_name	#mac_var_value
Test0	%motor1%	M5	%motor2%	M6

 

In this case, you insert the macro Test0, the variable %motor1% is changed by M5 and the variable %motor2% is changed by M6.

 

The second method is better if you use the same names of the variables for all the macros. Then, in this method, on the header you specify the name of the variable, for instance %VAR1%, and on the different rows, the value that, this variable has, for this inserted macro. For example:

#mac_name	%var1%	%var2%
Test0	M5	M6
Test1	V3	V4

 

In this case, the first line inserts the macro Test0, changes the variable %var1% by M5 and %var2% by M6 and on the second line, the macro Test1 is inserted, changing in this case the variable %var1% by V3 and variable %var2% by V4.

 

Remark: The names of the variables is not case sensitive.

Using formula with the variables

You can use a formula to automatically generate the value during the automatic generation of drawing. The formula will be identified by two characters '{ }', one at the beginning '{' and the other at the end '}'. The content between those two characters will be a formula, which can be like any formula we have in the software. Variables in the formula must be formatted the same way as the one in the Excel file (%VARIABLE%).

 

For example, set the mark of a component with this value: {"PP_" + %VARIABLE% + "RR" + %VARIABLE2%}

When inserting the macro twice, through Excel generation the result will be  PP_33RR12 the first time and PP_33RR13 the second time.

If in the Excel file we have two lines with:

%VARIABLE% set to 33 and %VARIABLE2% set to 12

%VARIABLE% set to 33 and %VARIABLE2% set to 13

Managing wire section

In the Wire properties dialog box of the macro, enter the variable %SECTION% for the Section or gauge field. In Excel, you can add an optional column, %SECTION% and input the value of the wire size.

Managing Origin - destination arrows

Consider two macros with an origin arrow placed in one macro and a destination arrow placed in the other macro. When you choose to insert both these macros using excel automation, you can establish the connection between origin and destination arrows in the two macros.

In the macros, insert origin or destination symbols in which you enter the same variable (for example: %Group1%) as the group name.

In the excel file, add a column using this variable. In this column, enter the group name. All the symbols with the same group name will be automatically associated.

Replacing a symbol while inserting a macro

Consider a symbol inserted in a macro. When you insert this macro from excel automation, you can replace this symbol.

Edit the macro and open the properties of the symbol. Enter the variable, for example: %SYMBOL_NAME% in the Excel automation variable field.

in the excel file, add a column using this variable (%SYMBOL_NAME% in our example). In this column, enter the name of the symbol that you want to use.

 

#mac_name	%SYMBOL_NAME%
Test0	TR-EL002
Test0	TR-EL035

 

In this example, you insert the macro Test0, one time with the TR-EL002 symbol and a second time with the symbol TR-EL035, even if another symbol has been defined in the Test0 macro.

Install Excel add-in

To help you to create the Excel file, you can use an add-in.

 

Menu: Import/Export > Excel Automation > Install Excel add-in

 

The installation program of the Excel add-in automatically starts. If not, select the EwExcelAddIn.vsto file.

Automatic generation of drawings

After having created the Excel file, you can import it in SOLIDWORKS Electrical to automatically generate the drawings.

 

Menu: Import/Export > Excel Automation

 

A selection file dialog box opens allowing you to select the Excel file.

The books, folders and drawings are automatically created, macros are inserted in these drawings and variables are replaced by the values.

 

SOLIDWORKS Electrical does not verify if the drawings are existing or if they contain other electrical entities. In this case, the macros will be inserted, regardless of existing entities of the drawing.

 

A report dialog box opens to display the import result.

Example file

1.     To use this example, we suggest that you create a new electrical project.

2.     Download the manufacturer catalog, Demo_Simple.part.tewzip and unarchive it in the Manufacturer part management.

3.     Download the macros, Demo_Simple_Macros.macro.tewzip and unarchive it in the Macro management.

4.     Open the macros to show the variables.

5.     Download the Demo_Simple.xlsx file and open it to show its structure.

6.     Launch the Excel AUTOMATION command to import the Excel file.