Simple VBA Excel to EA importer v4
Version 4 on the Excel to EA Importer now introduces a feature to export Tagged Values to Excel, edit them in Excel and import the changes back into Enterprise Architect.
The Excel to EA importer is a rather simple tool to import stuff into EA. It has been available on the Sparx Community Site since 2009 and it has always been one of the most popular resources.
Version 2 was a small improvement allowing for different types of Elements.
Version 3 added the feature to import the glossary into EA.
Now in version 4 we have yet another tab Tagged Values that will be used to export and import tagged values.
Free download
Elements and Attributes
Columns A till F are used in the sheet ElementsAndAttributes . Fill in the the columns with your own data.
- Type
Fill in the type of the Element you wish to import. This column should contain either Attribute or one of the EA Element types as defined in the documentation of EA.Element.Type. An attribute will be added to the last specified Element. - Name
The name of the element or attribute. - Stereotype
The stereotype of the element or attribute. Use null to specify an empty stereotype. - Description
The description of the element or attribute. - Attribute Type
The type of the attribute. This column should not be used for Elements. Use null to specify an empty type. - Attribute Length
This value will be added as a tagged value with the name length on the attribute. Again this is only for attributes.
Glossary
In the 2nd sheet Glossary you can fill in columns A until C with:
- Term
The term name is mandatory. - Type
The type of the term is mandatory as well - Meaning
The meaning of the term is optional.
Tagged Values
The Tagged Values sheet can only be used to edit existing tagged values, not for importing new tagged values into EA. It contains the following columns
- GUID
The unique identifier of the tagged value. Used to find the tagged value in the model. – Do not change. - Type
Used to indicate if the tagged value is on an element, attribute, operation or connector. – Do not change - Property
The name of the tagged value. This field can be used to rename tagged values - Value
The value of the tagged value. This field can be edited. - Notes
The notes of the tagged value. This field can be edited. - Owner
The name of the element that owns the tagged value. Used as a context indicator. Changing this field will have no effect.
Export/Import
Once all you data has been filled in you are ready to import the data into EA.
- Open the sheet you wish to import
- If you are on the ElementsAndAttributes sheet press the button Import, or run the macro (Alt-F8) importFromExcel
- If you are on the Glossary sheet press the button Import Glossary, or run the macro (Alt-F8) importGlossaryFromExcel
- On the Tagged Values sheet you can press the button Export Tagged Values, or run the macro (Alt-F8) exportTaggedValuesFromEA in order to get all tagged values into the sheet.
- After modifying the content of the tagged values press the button Import Tagged Values, or run the macro (Alt-F8) importTaggedValuesFromExcel and press start to start import.
Customizing
The code of this little tool is embedded as VBA in the document, and as such can be edited by anyone with basic programming skills.
To open the code editor press Alt-F11.
There are three major parts for the code
- ExcelImporter
The module that contains the importFromExcel macro. This is where you want to make changes. - EAConnector
A facade for EA’s API containing all kinds of useful functions. You probably don’t need to change anything here. - ExcelConnector
A facade for Excel with the code to get the values of the sheet. Again probably no need to change anything here.
Both the Excel file as the VBA code embedded in it is free for use. Just be kind and leave the reference to my name in the code if you use it.
How can i import packages in EA model??
& can i import root information?
Yes you could if you changed the VBA code to handle packages as well. Instead of using
EA.Package.Elements.AddNew()you would have to useEA.Package.Packages.AddNew()I’m not sure what you mean by “import root information”
Hi Geert,
Great tool! Just two questions:
1) Any ideas on how to define the database type of the class via Excel?
2) The length of the field is not filled in correctly, it gives a 0 value.
Hi Geert,
I have a question regarding importing tables and column values. Currently, if I were to use the excel import file, and set type to class and attribute, it would create the class with all the attributes underneath it in a EA package.
What if I wanted to import a data element: table and populate 20 or so tables with data that would belong in the columns? What is the best way to do this using the excel import function? Can it be done using the importer?
I currently have almost 500 rows and rather then import as a class and attribute, I wanted to create tables I could link and show relationships. I am also a novice at coding so if a change is required in the vb code, could you please provide specific details.
Thanks
Ray
Hi Geert,
I attempted to run a simple test on the tagged values update.
I suppose this is intended to update definitions available from the UML Types > Tagged Value Types list, is that correct?
When I run the “exportTaggedValuesFromEA” macro, I get an error : user defined type not defined. It stops on the convertXMLtoArray(xmlString) function -> and selects Dim xDoc As DOMDocument.
Are you experiencing the same issue?
Hi,
I am experiencing the same issue, I get the same error when running exporttaggedvaluesfromEA.
Any tips on how to solve this?
Regards,
Steven
Hi Steven,
The error is probably due to the XML library that is not available.
Windows usually comes with a number of similar XML libraries, but the exact version available may differ.
Try going into the VBA editor (Alt-F11) and choose Tools | References.
There you will probably see the entry Microsoft XML, v6.0 as being missing. Scroll down and choose one of the other Microsoft XML libraries that are available. The exact version is not really relevant for the basic usage in this tool.
Geert
Hi Geert,
Thanks! I found it in the end. The Microsoft XML, v6.0 was checked… Googling this led me to the tip to refer to the DOM object as DOMDocument60. This worked for me.
Not really sure why, though,,,
Guillaume, I am having the same problem. Have you solved it?
Hi Geert, I may be missing something – but how do you specify associations between classes (for import)?
If possible – what about other types of relationships (e.g. aggregation, realize, …)
Hi Geert,
When I invoke the macro to Import from Excel, the macro asks me to select a package. How do I enter the package name?
Hi Henry,
You need to select a package in the project browser in EA prior to starting the macro in Excel.
This package will be the package the contents will be imported to.
Hi Geert,
I am still seeing the same problem. Let me describe what I am doing in more detail:
1. I created a copy of eaexcelimporter_v4.xls in the same folder as the eap file and renamed the copy;
2. I opened the copy and edited the ElementsAndAttributes tab to contain a single element of type ‘Class’ with the ‘Activity’ stereotype, along with a Description and with the AttributeType set to ‘null’ (no entry for the AttributeLength);
3. I closed the excel file;
4. I opened the eap file and selected the package in the project browser;
5. I opened the importer file, clicked on the ADD-INS tab, and selected the ‘Import’ macro;
6. A window pops up in Excel with the message, “Please select a package,” and an ‘OK’ button.
Since there is no entry field in the pop up window, I am stuck.
Henry,
Are you running one of the applications as administrator?
The Excel importer will connect to EA using the running ea.exe process.
Therefore you should be running both applications using the same user and admin mode, otherwise the ea.exe process will not be visible to the Excel macro.
Other things to check that might cause this type of problem:
– Do you any any lingering instances of EA open (check task manager)
– Is either one of your application running remotely?
Hi Geert,
Following up on my post from 23 March – Is there a way to import connectors / associations using your tool?
Best regards/
Hi Philip,
You’ll have to write some extra VBA code to do that.
I know it’s been done before, so it is definitely possible.
Geert
Geert,
Both EA.exe *32 and EXCEL.EXE are running under my user name. There are no other lingering instances of either Excel or EA. Neither application is running remotely.
I ran the glossary import macro and it was successful. After running the glossary import macro, the import macro ran successfully.
I am not sure why this worked, but I suspect once the connection was established by the glossary import macro (which already knows where the glossary is in the open EA repository), EA became visible to the Excel Import macro.
Hi Geert. I’m using your Excel Exporter/Importer (thanks for that!) to update loads of attribute tags at once. My model is quite large (2000 attributes with 30 tagged values against each), so a full export to populate the attributes and tags in Excel would take a long time. Is it possible for me to modify the code to make it only export the attributes and tagged values for classes within a specific package? If so, so I need to specify the package path, or just the package name? I’m afraid I’m not very good with code stuff!
Many thanks,
Luke
Sure, you can definitely do that.
The export feature uses an SQL query to get the info.
What you would need to do is edit the SQL query in the operation EAConnector.exportTaggedValues() and add a where clause limiting the t_object.PackageID to a certain ID.
If needed you can always hire me to make the changes for you.
Geert
Hi Geert,
thanks for the awesome tool. Is there a way to add Signals? They’re not specified as element type but with the built in csv-Import it works. Any idea?
Thanks a lot,
Jan
Hi Jan,
Apparently I haven’t included “Signal” in the list of valid EA element types.
You can do that yourself by editing the VBA code.
– Press Alt-F11 to enter the VBA development environment
– Open Class Modules | EAConnector
– Scroll down to the function
isValidElementType– Add
Case "Signal"to the list of cases.isValidElementType = True
I’ll make sure to include it in the next release.
Geert
It works. Thank you!
Geert, first of all thank you very much for the good work you’re sharing with us, thus making us all improve.
I have two questions:
Since it is a feature that seems to interest many users , do you plan to include connectors creations/update in the next version of this excel vba tool ?
If so, meanwhile I’ve been struggling for the last weeks trying to figure a way to generate connectors from a csv or excel that contains the elements, the connectors and their type.
As I read your different comments throughout your website, It seems there are two ways of doing it :
– one would be modify the source of your vba code.
– the other would be to modify your other script “Link to Domain Model.xml”.
Which one should I go with? Could you please indicate which portion of the code should I modify ?
PS : I am average vbscript code (no in Jscript of Java).
Hi Geert,
Quick question about tagged values importer. Ive created a classes and attributes and imported them, which is fine. But when i try ot update the tagged values by adding the correct guid for the value nothing happens. Am i doing something wrong? (Im getting the update thing and i press start, but theres no close message when its done ?)
I have even tried updating a tagged value against an attribute, but that also doesnt see to update when i try using the macro
Hi,
Have you exported the tagged values first and then updated the sheet and imported again?
I think that is the best way to go about it.
Geert
Hi Geert, Just trying to work this again. Im trying to export the tagged values (which I don’t think there are any in my sheet currently) and its giving me the error that there is a Type mismatch ? Also that I might not have opened EA, which is untrue.
Many thanks
D
Hi! For anyone using version 15 of EA. The attribute class InitialValue property is now called Default.