How to connect visio shapes on page by passing the name of the shape to connect to from a variable?

[Michael Ziegler]

I am trying to connect the visio shapes in my drawing. I am using autoconnect. I have a loop that goes thru all the visio shapes in my drawing. It goes thru the values in my range and compares them to the shape name, if the shape name matches then it should connect to the shape who has the name stored in the offset (0, 2) of my range variable but I’m having problems passing the variable to the script. If I do a debug print for the variable that stores the shape names to connect to then it prints to screen the names of the shapes that the current shape in the loop needs to connect to. So it has the correct data.


Here is some of the code.

Dim conns As Range


    Dim connto_str As String


    Dim ew As Excel.Workbook


    Set ew = wbkInst.ActiveWorkbook


    Dim conns As Range


    Dim cel As Range


    Dim ws As Worksheet




    For Each ws In ew.Sheets


        Set conns = ws.Range("j3:j22")




        For Each cel In conns




            With cel


                c = cel.Value




                connto_str = cel.Offset(0, 2).Value  




            End With




            For Each node In ActivePage.Shapes


                If node.Name = c Then


                node.AutoConnect connto_str, visAutoConnectDirNone




                'Debug.Print connto_str




                Else


                End If




            Next node


        Next cel




    Next ws

I need to be able to pass the content of the variable to this statement.

node.AutoConnect connto_str, visAutoConnectDirNone

Thanks

 

[pbornemeier]

This probably can be cleaned up, but it works:

Sub TestConnect()
    'Code in Visio Module
    'Excel running with info document on active worksheet

    Dim appXL As Object
    Set appXL = GetObject(, "Excel.Application")
    
    Dim ew As Object
    Set ew = appXL.activeWorkbook
    
    Dim ws As Object
    Set ws = appXL.activesheet
    
    Dim conns As Object
    Set conns = ws.Range("A1:A3")  'Don't need to reassign inside For..next
    
    Dim cel As Object
    
    Dim connto_str As String
    
    Dim c As String
    
    Dim Node As Shape
    

    For Each ws In ew.Sheets
        For Each cel In conns
            With cel
                c = .Value
                connto_str = .Offset(0, 1).Value    'My 'To' data in adjacent column
            End With
            For Each Node In ActivePage.Shapes
                If Node.Name = c Then
                Node.AutoConnect ActivePage.Shapes(connto_str), visAutoConnectDirNone
                'Debug.Print connto_str
                Else
                End If
            Next Node
        Next cel
    Next ws
    
End Sub