Using VBScript to import Excel Files and generate diagrams automatically

[Andre_b_b]

Hi guys,
i have been working with Enterprise Architect for 2 months and my objective is, from a Excel file, generate a diagram within the relations between the elements(mainly "InformationFlow", "Derive","Usage"). In order to achieve this, i use the MDG Integration Office Add-in and i could do it, but i need to do the process in an automatic way: Run a script inside EA should be enough. I have developed a code in VBScript but it is not working - in fact, i can run the script but anything happens/changes in the software. Can you help me with the code or suggest any other way to achieve my objective? Unfortunately i only have 2 months to finish this project... Here i let the code that was written in the scripting Window of my Enterprise Architect.

Code: [Select]

option explicit

!INC Local Scripts.EAConstants-VBScript

'
' Script Name: import excel into EA
' Author:  Andre
' Purpose: import an excel file in order to generate a diagram and its elements
' Date: 19/06/2023
'
Sub importexcel()   


    ' Set the path and filename of the Excel file to import 
    Dim filePath
    Dim fileName
    filePath = "MyPathtomyFile" 
    fileName = "my_file.xlsx" 
     
    ' Set the name of the package to import the data into 
   Dim packageName
    packageName = "New Package" 
     
    ' Set the name of the object profile and connector profile to use 
   Dim objProfileName
   Dim conProfileName 
    objProfileName = "my_objprofile_name" 
    conProfileName = "my_conprofile_name" 
     
    ' Import the data from the Excel file 
    Dim repository As EA.Repository 
    Set repository = GetObject("MyPathtomyFile", "EA.App").Repository 
    repository.ImportCSVEx filePath & fileName, objProfileName, conProfileName, packageName, False, False, False, False, False, False 
     
    ' Create a new diagram to represent the imported data 
    Dim diagram As EA.Diagram 
    Set diagram = repository.Diagrams.AddNew("name_of_diagram", "ActivityDiagram") 
     diagram.PackageID = repository.GetPackageByGuid(repository.Models.GetAt(0).PackageGUID).PackageID 
     diagram.Update 
     
 
 

    ' Find the objects that were imported and add them to the diagram 
    Dim objects As EA.Collection 
    Set objects = repository.GetElementsByQuery("SELECT Object_ID FROM t_object WHERE Stereotype = 'YourStereotypeName' AND Package_ID = " & repository.GetPackageByName(packageName).PackageID) 
    Dim obj As EA.Element 
    For Each obj In objects 
        Dim diagramObject As EA.DiagramObject 
        Set diagramObject = diagram.DiagramObjects.AddNew("", "") 
        diagramObject.ElementID = obj.ElementID 
        diagramObject.Update 
    Next   
     
    ' Find the connectors that were imported and add them to the diagram 
    Dim connectors As EA.Collection 
    Set connectors = repository.GetElementsByQuery("SELECT Connector_ID FROM t_connector WHERE Package_ID = " & repository.GetPackageByName(packageName).PackageID) 
    Dim con As EA.Connector 
    For Each con In connectors 
        Dim diagramLink As EA.DiagramLink 
        Set diagramLink = diagram.DiagramLinks.AddNew("", "") 
        diagramLink.ConnectorID = con.ConnectorID 
        diagramLink.Update 
    Next 
     
    ' Refresh the view to show the imported data and the new diagram 
    repository.RefreshModelView (packageName) 
     
End Sub 


[Geert Bellekens]

"Is not working" is not a very useful problem statement.

The trick is to start small. make a small part that works, then add another part, work on it until it works, and then continue.

One thing I see is wrong in your code is the way you try to create a diagram

Code: [Select]

Set diagram = repository.Diagrams.AddNew("name_of_diagram", "ActivityDiagram")
There is no such thing as Repository.Diagrams, so that's never going to work.
Instead you create them from the Package.Diagrams collection.
If you do, you won't need this nonsense

Code: [Select]

diagram.PackageID = repository.GetPackageByGuid(repository.Models.GetAt(0).PackageGUID).PackageID
Geert

[Eve]

Have you used ChatGPT or similar to write that script?

It looks superficially like an EA script, but I don't think any part of it is reasonable.

• ImportCSVEx doesn't exist anywhere in our API (neither does ImportCSV)
• The repository.Diagrams already mentioned
• The type parameter for Diagrams.AddNew "ActivityDiagram" isn't a valid type
• repository.Models.GetAt(0) denotes a root package which isn't allowed to have diagrams
• GetElementsByQuery takes a search name and search term
• GetPackageByName doesn't exist anywhere in our API (and it looks like it was intended to be created somehow)
• RefreshModelView takes a package id not a name.

My advice. Throw the whole script away and actually look at example scripts and documentation when you start writing.

[Geert Bellekens]

I'm guessing something like ChatGPT has been involved in producing this script.

Now while that can be a useful thing to speed up the development once you know what you want to write, I'm afraid it's not a substitute for getting to know the environment and the API.

Geert

[Andre_b_b]

Ok, thanks for your help; i will go step by step then
I do not understand and i can't find how to add an element from VBScript in EA.
Should i use this command?

Code: [Select]

Set newElement = eaElement.AddNew(name,type)
I want to read the Excel and generate from the columns new elements.

[qwerty]

Well, that's not the way to do it. Take a lesson or read something to learn the API. You won't get the codez here.


q.

[Geert Bellekens]

Ok, thanks for your help; i will go step by step then
I do not understand and i can't find how to add an element from VBScript in EA.
Should i use this command?

Code: [Select]

Set newElement = eaElement.AddNew(name,type)
I want to read the Excel and generate from the columns new elements.

Adding something is always done on the collection of the owner.

So if you want to add a diagram to a package you do package.Diagrams.AddNew(), if you want to add an attribute to an element, you do Element.Attributes.AddNew()

The whole API is documented here: https://sparxsystems.com/enterprise_architect_user_guide/16.1/add-ins___scripting/reference.html

Geert

[Andre_b_b]

Ok Qwerty, thanks for your attention.
Would you recomend a course or some materials i could use to learn?
 Unfortunately, i have been looking for materials, but i did not find a lot. Iam not expert in Programming and in Enterprise Architect, but i do want to learn and produce content and important tools for me.

[Andre_b_b]

Thanks Geert. I will read and i will arrive with more questions

[qwerty]

Since you asked: see my scripting book (link below). I think it's the easiest way to get accustomed with the API.

q.

[Andre_b_b]

Thanks Qwerty!

[Andre_b_b]

Hi guys,
just to say that i could make an interface with Excel, from a VBscript in Enterprise Architect, in order to generate the elements in Enterprise Architect within a package with the value of the cells of the Excel. Another script is udes to show the elements in the diagram. Next step is to discover how to add the connections between the elements.

[Geert Bellekens]

Next step is to discover how to add the connections between the elements.

Element.Connectors.AddNew()

Geert

[Andre_b_b]

Thanks Geert. But how do i put the Source, Target and Type of the connector? Shoud i use the name of Source and Target or their GUIDs?

[Geert Bellekens]

The source is the element you create it on.
The target can be set with the connector.SupplierID

Geert