Hide Table of Contents

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_manual

Manual or Automatic Tag

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_manual

Manual or automatic tag

No

#bun.tra_0.xx

The description of the book 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_tagmanual

Manual or automatic tag

No

#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_tagmanual

Manual or automatic tag

No

#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

 

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.



Provide feedback on this topic

SOLIDWORKS welcomes your feedback concerning the presentation, accuracy, and thoroughness of the documentation. Use the form below to send your comments and suggestions about this topic directly to our documentation team. The documentation team cannot answer technical support questions. Click here for information about technical support.

* Required

 
*Email:  
Subject:   Feedback on Help Topics
Page:   Excel Automation
*Comment:  
*   I acknowledge I have read and I hereby accept the privacy policy under which my Personal Data will be used by Dassault Systèmes

Print Topic

Select the scope of content to print:




x

We have detected you are using a browser version older than Internet Explorer 7. For optimized display, we suggest upgrading your browser to Internet Explorer 7 or newer.

 Never show this message again
x

Web Help Content Version: SOLIDWORKS Electrical 2022 SP05

To disable Web help from within SOLIDWORKS and use local help instead, click Help > Use SOLIDWORKS Web Help.

To report problems encountered with the Web help interface and search, contact your local support representative. To provide feedback on individual help topics, use the “Feedback on this topic” link on the individual topic page.