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

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.

Item Description
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 automation lets you automatically reset undefined macro variables. To reset undefined macro variables, click SOLIDWORKS Electrical > Configurations > Project. In the Electrical Project Configuration dialog box, on the General tab, under Excel automation, select Reset undefined macro variable. When you select this option, the %xxx% variable does not remain in the inserted macro. It is replaced by one of the following:
  • An empty string
  • A removed object
  • An associated default object like a function or a location.

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.

To create a new Excel file for automation, click Import/Export > Excel Automation > Create new Excel file for automation .

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.

Items Description
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 must manage are corresponding to the macro name and positions.

Table 1. 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:
Table 2. 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
Table 3. 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
Table 4. Folders
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
Table 5. 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
Table 6. 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

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.
The names of the variables are 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.
Auto-connect wires To use this option, you must select Auto-connect macros parameter in the General tab in the electrical project configuration.

When you use the Excel Automation to create schemes, you can automatically connect the wires or the symbols between the existing scheme and the macro that you insert.

The elements that you want to connect must be aligned. You can connect wires to symbols or wires to wires.
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. To install it, click Import/Export > 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. Click 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, create a new electrical project.
  2. Download the manufacturer catalog, Demo_Simple.part.tewzip and unarchive it in Manufacturer part management.
  3. Download the macros, Demo_Simple_Macros.macro.tewzip and unarchive it in 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.