Auto draw network topology from excel data import

[seven80]

It is possible to create automatically a complete network topology from an excel file?
In my excel I have devices and connections information.
I imported all data in visio but now I only can link external data to a shape and manually connect the device shape with others.
My network is a bit large and I would like avoid to connect manually all the devices.

[wapperdude]

Found the following on internet...

From the forum:
http://visguy.com/vgforum/index.php?topic=1100.msg6247#msg6247

From non-forum:
http://www.addictivetips.com/microsoft-office/create-diagrams-in-ms-visio-2010-by-linking-excel-spreadsheet/

https://support.office.com/en-us/article/Create-a-detailed-network-diagram-by-using-external-data-in-Visio-Professional-1d43d1a0-e1ac-42bf-ad32-be436411dc08

HTH
Wapperdude

[seven80]

Thanks but I didn't found how to create automatically connection between shapes.
From external data I have also the connections information in a column defined "link to".
Can I automatically connect the imported shapes each others?

[Nikolay]

Long ago, when computers were big and programs were small, Visio supported creating diagrams from excel, like you describe (Visio 5, I believe?).
But those days are gone.

As of my knowledge, the last attempt to bring this sort of functionality back to life was made by Saveen Reddy,
https://blogs.msdn.microsoft.com/saveenr/2008/06/04/visio-using-msagl-to-create-visio-flowcharts-programmatically/
http://viziblr.com/visio/
https://github.com/saveenr/VisioAutomation

You can also try you luck with "org chart" wizard, but it's a tough guy, and it's really hard to teach the old dog new tricks.

There is one more addin out there. To be honest, I couldn't make it work, but maybe you could:
http://www.calvert.ch/graphvizio

[seven80]

I found and corrected this code to connect shapes from excel data file. I hope it will be useful for others.

Code Select Expand

Public Sub DrawSystem()

    Dim strConnection As String
    Dim strCommand As String
    Dim strOfficePath As String
    Dim vsoDataRecordset As Visio.DataRecordset
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=" + "C:\example.xls;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=34;"
    strCommand = "SELECT * FROM [Sheet1$]"
    Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Objects")

Dim stnObj As Visio.Document
Dim mastObj As Visio.Master
Dim pagsObj As Visio.Pages
Dim pagObj, activePageObj As Visio.Page
Dim shpObj As Visio.Shape
Dim shpFrom As Visio.Shape
Dim shpTo As Visio.Shape

Set stnObj = Documents.OpenEx("Basic Shapes.vss", visOpenDocked)

Set pagObj = ThisDocument.Pages.Add()

Dim lngRowIDs() As Long
Dim lngRow As Long
Dim lngColumn As Long
Dim varRowData As Variant

Debug.Print "PROCESSING ENTITY RECORDS"
lngRowIDs = vsoDataRecordset.GetDataRowIDs("")

Set mastObj = stnObj.Masters("Rectangle")

For lngRow = LBound(lngRowIDs) To UBound(lngRowIDs)

    varRowData = vsoDataRecordset.GetRowData(lngRow)

    If varRowData(2) = "ENTITY" Then

        Set shpObj = pagObj.Drop(mastObj, lngRow / 2, lngRow / 2)

        shpObj.Name = varRowData(3)
        shpObj.Text = varRowData(7)
        shpObj.Data1 = varRowData(3)
        shpObj.Data2 = varRowData(7)
        shpObj.Data3 = varRowData(8)

        shpObj.Cells("Width") = 0.75
        shpObj.Cells("Height") = 0.5

    End If

Next lngRow

               
lngRowIDs = vsoDataRecordset.GetDataRowIDs("")

Set mastObj = stnObj.Masters("Dynamic connector")

For lngRow = LBound(lngRowIDs) To UBound(lngRowIDs)

    varRowData = vsoDataRecordset.GetRowData(lngRow)
    Debug.Print ("!ddd!!" & varRowData(2))

    If varRowData(2) = "LINK" Then

        Dim fromName As String
        fromName = varRowData(4)

        Dim toName As String
        toName = varRowData(5)

        Dim conName As String
        conName = varRowData(6)


        Set shpCon = pagObj.Drop(mastObj, 2 + lngRow * 3, 0 + lngRow * 3)
        varRowData = vsoDataRecordset.GetRowData(lngRow)

        shpCon.Name = conName
        shpCon.Text = varRowData(7)

        Set shpFrom = ActivePage.Shapes(fromName)
        Set shpTo = ActivePage.Shapes(toName)
        shpFrom.AutoConnect shpTo, visAutoConnectDirNone, shpCon
    End If

Next lngRow
End Sub



to test script use this excel example

Code Select Expand


1,,ENTITY,A,,,1,1: A,ONE
2,,ENTITY,B,,,2,2: B,TWO
3,,ENTITY,C,,,3,3: C,THREE
13,1,LINK,LINK1,A,B,13.1,13.1: LINK1,LINK1
13,2,LINK,LINK2,A,C,13.2,13.2: LINK2,LINK2
13,2,LINK,LINK2,C,B,13.2,13.2: LINK2,LINK2


[Tom Barton]

I am fairly new to both Visio and VBA, so I hope this question is not too basic... I have been looking for an automated routine to help manage a number of network diagrams, and this would appear to fulfill the need.

I have been trying to run the code presented, but am totally unable to get it to read the Test Data. Of course, I have changed the link to the data for my environment, etc. but simply get a message that it cannot open the data file. Is there something I am missing?