Author Topic: Dump list of all entities to flat file - crow's foot diagram  (Read 261 times)

0 Members and 1 Guest are viewing this topic.

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Dump list of all entities to flat file - crow's foot diagram
« on: November 06, 2018, 05:40:08 AM »
Hi,

i am using VBA only in Excel, but now i am using Visio and need some help.
I want to export all entities into csv/excel file.

Can you help with some useful code?

Thank you,
Best,
Jacek Antek

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #1 on: November 06, 2018, 08:44:33 AM »
hi Guys,

I have something :)

Code: [Select]
Sub ExportExcel()

Dim objExcel As Object
Dim objWorkbook  As Object
Dim strPAthfile As String
Dim docsObj As Visio.Documents
Dim win As Visio.Window
Dim shp As Visio.Shape
Dim pagObj As Visio.Page
''Set objExcel = CreateObject("Excel.application")
''Set wb = CreateObject("Excel.workbook")

Set pagObj = ThisDocument.Pages.Item("Diagram")

For Each shp In pagObj.Shapes
    If shp.Name Like "Entity*" Then
        Debug.Print shp.Data1
    End If
Next shp

in this code i found page "Diagram" and i am looping through each shape.
I noticed that i can refer to entity using shape.name property.

How to get all atributes from entity? And how to know it is Primary Key or Foreign?

Best,
Jacek

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #2 on: November 07, 2018, 06:08:39 AM »
Hi,

No Visio code masters here?

I managed to get all shapes connected to Entity which is our containter here.
And change text for each atribute inclusing Entity text :)
I know i am boss :D
Please see the code:

Code: [Select]
Sub newone()

Dim containerID As Variant
Set pagObj = ThisDocument.Pages.Item("Diagram")
' Set pagObj = Application.ActivePage
Dim shp As Visio.Shape
Dim shpSub As Visio.Shape
Dim memberId As Variant

For Each containerID In pagObj.GetContainers(visContainerIncludeNested)
    Set vsoContainerShape = pagObj.Shapes.ItemFromID(containerID)
   
   
    For Each memberId In vsoContainerShape.ContainerProperties.GetMemberShapes(visContainerFlagsDefault)
    Set vsoShape = ActivePage.Shapes.ItemFromID(memberId)
    Debug.Print vsoShape.ID
    Set vsoCharacters1 = Application.ActiveWindow.Page.Shapes.ItemFromID(vsoShape.ID).Characters
    vsoCharacters1.Begin = 0
    vsoCharacters1.Text = "ok"
Next
   
'    For Each shp In vsoContainerShape.Shapes
'    Debug.Print shp.ID
'    Debug.Print shp.Name
'    Application.ActiveWindow.Page.Shapes.ItemFromID (shp.ID)
''    Set shp = Application.ActiveWindow.Page.Shapes.ItemFromID(shp.ID)
'    Set vsoCharacters1 = Application.ActiveWindow.Page.Shapes.ItemFromID(shp.ID).Characters
'    vsoCharacters1.Begin = 0
'    vsoCharacters1.End = 6
'    vsoCharacters1.Text = "111"
'    Next shp
   
Next


End Sub

What i need now i have to somehow find all related attributes and types of relationships for them.
It can be 1 to 1, 1 to many and other relationships.

Please help with that,
Best,
Jacek

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #3 on: November 08, 2018, 12:17:39 PM »
Not help here?

Best,
Jacek

Yacine

  • Hero Member
  • *****
  • Posts: 2509
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #4 on: November 08, 2018, 02:15:57 PM »
Hello Jacek,
none of the core members draw ERDs regularly. So it would help if you could narrow your problem to the very Visio specific problem.

From what I found out looking at Visio's crow-foot connectors (shapesheet) is that they don't carry any data in the custom property section. Every thing is done visually. If you need to read them out, then the line begin and end numbers are the data to look for. You would of course map the numbers to an identifying code of your choice ( eg line_end = 21 --> "one to many" or similar).

To figure out which shape is connected to which, you may have a look at Wapperdude's work in this forum. He's done a lot of VBA coding handling connectors.

Once you've stored all the necessary data in a data structure (array, collection, ...) it is easy to write it to an Excel file.
This google search throws right away the following topic
https://www.google.de/search?newwindow=1&safe=off&source=hp&ei=1IrkW86GGOSOrgSnh5foBQ&q=export+excel+vba+site%3Avisguy.com&oq=export+excel+vba+site%3Avisguy.com&gs_l=psy-ab.3...4634.13810.0.14005.33.29.0.3.3.0.173.2987.9j18.27.0....0...1c.1.64.psy-ab..3.20.2164.0..0j35i39k1j0i131k1j0i3k1j0i203k1j0i22i30k1j33i22i29i30k1.0.tDm8ZQmp6jM

http://visguy.com/vgforum/index.php?topic=7297.0

HTH,
Y.
Yacine

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #5 on: November 09, 2018, 03:35:03 AM »
Hi,

thank you.
I attached Viso file with crow's foot diagram - module MacroOutput..

Quote
From what I found out looking at Visio's crow-foot connectors (shapesheet) is that they don't carry any data in the custom property section. Every thing is done visually. If you need to read them out, then the line begin and end numbers are the data to look for. You would of course map the numbers to an identifying code of your choice ( eg line_end = 21 --> "one to many" or similar).

o wow, thank you for tip. How to refer in code to shapesheet?

Quote
Once you've stored all the necessary data in a data structure (array, collection, ...) it is easy to write it to an Excel file.
This google search throws right away the following topic
https://www.google.de/search?newwindow=1&safe=off&source=hp&ei=1IrkW86GGOSOrgSnh5foBQ&q=export+excel+vba+site%3Avisguy.com&oq=export+excel+vba+site%3Avisguy.com&gs_l=psy-ab.3...4634.13810.0.14005.33.29.0.3.3.0.173.2987.9j18.27.0....0...1c.1.64.psy-ab..3.20.2164.0..0j35i39k1j0i131k1j0i3k1j0i203k1j0i22i30k1j33i22i29i30k1.0.tDm8ZQmp6jM

exactly, i now how to do this and i tested it.

Best,
Jacek




Yacine

  • Hero Member
  • *****
  • Posts: 2509
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #6 on: November 10, 2018, 03:01:34 AM »
I had some time to play, but was too lazy to dive into your code.
I wrote a new routine for finding the connections, but yours based on the containers is just fine too. The addition to your code, is the use of the "connectedShapes" function. The resulting array are the connected attributes. My approach using the connections collection of the page has the advantage, that you get also the connectors involved - needed to determine the kind of relation.
I leave the remaining work up to you. ;)


Code: [Select]
Sub getRelations()

    Dim conn As Connect
    Dim pg As Page
    Dim sourceShp As Shape  ' an attribute shape
    Dim targetShp As Shape  ' an attribute shape
    Dim connShp As Shape    ' a connector shape
   
    Dim arShpIDs() As Long
    Dim i As Integer
   
    Set pg = ActivePage
   
    For Each conn In pg.Connects
'        Debug.Print conn.Index      ' conn is the "connection" between 2 shapes
'        Debug.Print conn.FromSheet  ' that's the connector
'        Debug.Print conn.ToSheet    ' that's the shape connected to
        Set connShp = conn.FromSheet
        Set sourceShp = conn.ToSheet
       
        arShpIDs = sourceShp.ConnectedShapes(visConnectedShapesIncomingNodes, "")
        For i = 0 To UBound(arShpIDs)
            Set targetShp = pg.Shapes.ItemFromID(arShpIDs(i))

            begin_type = connShp.Cells("BeginArrow")
            Select Case begin_type
            Case 29:
                t_begin_type = "zero or more"
            Case 28:
                t_begin_type = "one or more"
            Case 25:
                t_begin_type = "one"
            Case 30:
                t_begin_type = "zero or one"
            Case Else:
                t_begin_type = "other type"
            End Select
            t_begin_type = t_begin_type & " (" & begin_type & ")"
           
            end_type = connShp.Cells("EndArrow")
            Select Case end_type
            Case 29:
                t_end_type = "zero or more"
            Case 28:
                t_end_type = "one or more"
            Case 25:
                t_end_type = "one"
            Case 30:
                t_end_type = "zero or one"
            Case Else:
                t_end_type = "other type"
            End Select
            t_end_type = t_end_type & " (" & end_type & ")"
           
            Debug.Print "Source shape: "; sourceShp.Text; " / relation: "; t_end_type
            Debug.Print "Target shape: "; targetShp.Text; " / relation: "; t_begin_type
            Debug.Print "Relation connector: "; connShp
            Debug.Print
        Next i
    Next conn
End Sub
« Last Edit: November 11, 2018, 06:31:36 AM by Yacine »
Yacine

jaryszek

  • Jr. Member
  • **
  • Posts: 15
Re: Dump list of all entities to flat file - crow's foot diagram
« Reply #7 on: November 27, 2018, 07:29:31 AM »
Thank you Yacine,

wow what a great code!
Sorry for my late answer, i had new task but now i hope will return.

Now i have to find out how to get ContainerID for Each source shape and target shape.
And take care about these shapes which have not any relationships.

Best,
Jacek