Auto generate network diagrams from excel data (.csv / exsl)

[lachieW]

Hi All.


Thanks alot.

[Surrogate]

IMHO the task is more difficult than you think! If we are talking about diagram more than 10 devices.

The hardest part is placing the shapes on the page...
Many years ago I tried to do something like this and realized that it was too complicated for a technical writer like me.

[wapperdude]

I agree with Surrogate.

It's more than just plopping shapes down and connecting them.  It's arranging the shapes such that the diagram is visually intelligible, not a plate of spaghetti and meat balls.  Add to that, Visio does not use any connector routing AI.  This will result in less than ideal looking routes.

Even if you started with a nice drawing, translated it to Excel, and then rebuilt the drawing based upon the Excel entries.  The resulting routes most certainly won't flow as the original.

[Yacine]

Surrogate and Wapperdude are right if the intention is to get a perfect drawn diagram.
If the perfect placement is not part of the task - e.g. as preparation step or for dynamic display of smaller diagrams - you can play with Visio's auto-layout function.
https://docs.microsoft.com/en-us/office/vba/api/visio.page.layout

Back to your original question.
There are different methods to read an Excel file.

Here's a snippet of an easy method using early binding - you need to set a reference to the excel library in VBA.

Code Select Expand

Public Sub excelImport(filename as string)
On Error GoTo onerror
    Dim xlsWorkbook As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    Dim shp As Visio.Shape
    Dim rows As Integer
    Dim shpID As String
   

    Set xlsWorkbook = Excel.Workbooks.Open(fileName)
    Set xlsSheet = xlsWorkbook.Worksheets(1)
   
    rows = xlsSheet.Range("A65000").End(xlUp).row
    For row = 2 To rows
        shpID = xlsSheet.Range("P" & row).FormulaR1C1
        If Not shpID = "" Then
            temp = xlsSheet.Range("A" & row).FormulaR1C1
            'draw your diagram using the so collected temp value. Use more columns for more information.'
        End If
    Next row
   
    xlsSheet.Application.Quit

Exit Sub


And Google is your friend: https://www.google.com/search?q=visio+vba+read+excel+file