Import Visio Data Report from Excel and draw crows foot diagram

[jaryszek]

hi Guys,

i checked feature "Reports" and this is awesome, i got full report in Excel and now i can use some VBA to get desire loyout of this, nice!

https://postimg.cc/zbM6FSnm

I checked also topic from wapperdude (btw. - excellent knowledge!):

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

but this is acting strange and building something like this:
https://postimg.cc/jDSVK5qF

This is not building crows foot diagram from Excel report.

How can i achieve this? it should add entitites not single shapes and create connectors using width and heights provided in Excel...

Desired Result:
https://postimg.cc/Ff0wVJT1

How can i start? How import data from report?
How to do this properly?

First import data and run macro recorder when i am creating shapes?
But i have all data provided...

Maybe Experts here can build one small example how to do it? I have no experience with Visio objects, only with Excel and Access...

Please help,
Best,
Jacek

[Surrogate]

but this is acting strange and building something like this:

Hi, Jacek !
Why you mean that code by Mr. Wapperdude give strange result ?
In his post I can't find words that this code must create Crow Foot diagram. Really !

[jaryszek]

Hi!

I thought that this code will handle any diagram...
Can you help with drawing crows foot diagram?

How to start?

Get data import to visio like here?
https://postimg.cc/gLpKj2w1

and loop through each entity?
How to set up proper distances between entities ?

Any tips will be useful.
I didnt find any solutions specific for corws foot diagram...

Best,
Jacek

[wapperdude]

LOL.

The code is generic, providing building blocks as it were.  But each block must be customized for specific use.  As a side note, when the code was written, had no knowledge there was such a thing as a crows foot diagrams.  I've always been a little pigeon toed myself.

For your case, are asking for the code to place the shapes or are they pre-placed?  The former is quite involved.  Pre-placed is easier. 

A little preliminary work is necessary.  Place the shapes where desired.  Create and organize the Excel file.  Somehow, there needs to be some way to relate the Excel file data to each shape... could be by shape ID, shape position, perhaps a "key" entry in the shapesheet.  Try to avoid "naming" the shapes as this invariably leads to problems.  Once this preliminary organization is done, then the code can run thru the Excel file and send it to the desired shape.

Another approach if shapes aren't preset, would be to run the code as is, and then place the shapes.  The code could be modified to place a specific type of shape if there was perhaps a shape definition entry in the Excel file.

Just some thoughts.  There are probably a lot of additional options available.

[jaryszek]

Hi wapperdude,

thank you very much.
For code: i have diagram already done and i exported details to excel.
I would like to have possibility to import the excel with changes (for example with added new shapes), read data by Visio and draw proper shapes in proper places.
So reproduce Excel data into Visio drawing and add new shapes if there are.

I attached the sample Excel and screen from 2 entities in crows foot diagram as example in previous posts.

thank you for any help,
Best,
Jacek

[jaryszek]

anyone please?

Best,
Jacek

[wapperdude]

There are several obstacles to create your desired solution.
1) There is no connectivity info in the Excel file.  So, automatically connecting via code is not possible.
2) Where would each new placement be located?  A crows foot diagram has no restrictions upon location, e.g., no swim lanes.  So, the best that could be done is some generalized placement.  Then shape manually positioned.
3) Are all the Entity shapes (actually, these are lists, specialized containers) configured the same, i.e., same number and type of attributes?  If not, that's extra coding.
4)  The Excel file looks like it was generated by the Visio Inventory Report.  First glance this seems poorly organized.  Not your fault.  The Report seems to not reflect the true nature of the Crows Foot Diagram.  For example, it's not immediately clear which attributes are associated with which Entity.

How many Entity shapes are you trying to place?
Updating existing shapes might not be too bad, but a differently organized Report (Excel file might be needed.)

So, all in all, this isn't a trivial task.

[jaryszek]

Hi wapperdude,

thank you.

1) There is no connectivity info in the Excel file.  So, automatically connecting via code is not possible.

- what info do you mean and how to get it?
This is report genereted using wizard.

2) Then shape manually positioned.

- i will have about 30 or 40 shapes, it is to big to do it manually...

3) Are all the Entity shapes (actually, these are lists, specialized containers) configured the same, i.e., same number and type of attributes?  If not, that's extra coding.

No, you can have table with 3 atributes or with 5 or 10...

How many Entity shapes are you trying to place?

about 40.

Updating existing shapes might not be too bad, but a differently organized Report (Excel file might be needed.)

how to do it properly?

Best,
Jacek

[wapperdude]

This is a complex task.  There are no cookie cutter solutions.  There is no existing code for Crows Foot diagram.  The report wizard generates a generic Excel table that requires editing to make it better organized and understandable.  A year from now, the table that you post probably would be difficult to understand.  Plus it's incomplete.  Take a look at it.  Does it show what you need to know:  what attributes belong to which Entity?  How are the various Entity's related?  Are the relationships optional, single, multiple?

Where to start.  Clean-up that table.  For code development.  Start small and simple.  Use macro recorder.  Drop and entity shape.  Look at code.  Edit entity shape and look at code.  The forum and the web can be a resource for coding techniques.  But you need to take the pieces and put them together into a custom code for your needs. 

There's a lot of work involved here.  You have to do the work.  The forum contributors are volunteers.  They will assist, review, etc.  But don't expect them to do the work. 

[Surrogate]

This is a complex task.  There are no cookie cutter solutions...
There's a lot of work involved here.  You have to do the work.  The forum contributors are volunteers.  They will assist, review, etc.  But don't expect them to do the work.

I wrote same words in this cross-post.

[jaryszek]

Hi Guys,

thank you.
what about exporting data into Excel.
I thought that report feature can handle it but not.

How can i export it via vba?
Can you show example for one entity how to loop?

Best,
Jacek

[wapperdude]

I've been working on a solution as time permits.  Have a couple of details to finish up for formatting a basic "Entity" report.

In the meantime,  check out these two posts:
http://visguy.com/vgforum/index.php?topic=8686.0
http://visguy.com/vgforum/index.php?topic=8688.0

[wapperdude]

Here's test file with VBA code.  It only does a selected shape.  Because it generates a custom export to Excel, most everything is hardcoded: column titles, number of columns.  It will loop thru the selected shape and report on however many attribute shapes are present.  The code, as such, is "demo" code.  It needs editing, but there are comments to identify various sections within the code.  It does not save the Excel file, but that could be added.  It can be adaptable to loop thru Entity shapes. 

So, the basic functionality is provided.  Edit as necessary.  Ask questions if you get stuck.  Anyone is well to suggest/contribute/modify.

[jaryszek]

Hi wapperdude,

thank you very much you are awesome!
Sorry for my late answer, i had to change my current priority but of course i will review your code and test.
Seems awesome!

edit:
one problem i will have here, how to find connectors to second entity.
Maybe this should loop through all connectors first?

Best,
Jacek

[wapperdude]

Here's an additional post that I started.   Take a look at attached files.  Plus, it contains some additional links. 

David Parker has a nice post on getting listing of connections:  https://blog.bvisual.net/2009/09/16/listing-connections-in-visio-2010/. 

I took his code, and tweaked it a little.  The new version merely debug.prints the connector and it's source and target shapes.  So, look at his development or feel free to edit the code below.  It does work with Crows Foot diagram / shapes.

Code Select Expand


Public Sub ListGluedConnections()
'Original code developed by David Parker
'Code located @ https://blog.bvisual.net/2009/09/16/listing-connections-in-visio-2010/
'Minor code modification by Wapperdude, 2/4/2019
'
    Dim shp As Visio.Shape
    Dim connectorShape As Visio.Shape
    Dim sourceShape As Visio.Shape
    Dim targetShape As Visio.Shape
    Dim aryTargetIDs() As Long
    Dim arySourceIDs() As Long
    Dim targetID As Long
    Dim sourceID As Long
    Dim i As Integer
    For Each shp In Visio.ActivePage.Shapes
   
        If shp.OneD Then
            Debug.Print "Connector", shp.Name
            arySourceIDs = shp.GluedShapes(visGluedShapesIncoming2D, "")
            For i = 0 To UBound(arySourceIDs)
                Set sourceShape = Visio.ActivePage.Shapes.ItemFromID(arySourceIDs(i))
                Debug.Print "Src Shp: ", sourceShape.Name
            Next

            aryTargetIDs = shp.GluedShapes(visGluedShapesOutgoing2D, "")
            For i = 0 To UBound(aryTargetIDs)
                Set targetShape = Visio.ActivePage.Shapes.ItemFromID(aryTargetIDs(i))
                Debug.Print "Tgt Shp: ", targetShape.Name
            Next
        End If
    Next
End Sub