I am trying to accomplish the following with this code:
After the shapes have been dropped into Visio, I then look at columns L (source connection) and B (destination connection) to see where I should glue the ends of the dynamic connector in Visio. This does work when the data is consistent (no gaps/blanks) between the start and end of the populated range. However, if there is a blank cell/row anywhere in the middle, I get an "Unexpected end of file" error. See the following example:
In this case, after the loop is past L19, I can see by adding watches...that it cannot determine the ID values in column L and B, which is how I connect the end points of the connector to the Visio shapes.
Below is the entire module:
The piece in question is the last For loop which checks all populated cells in idRange (column L):
You can see from the watches, when the loop is currently is on L18, it can still properly determine the ID values in L18/B18.
For whatever reason I cannot graps, after I loop past a blank in column L, it does not seem to be able to execute this to what I would expect.
At this point, it can identify L20 = 7, but then grabs the "dynamic connector" for B20?
Again, when there are no breaks in data from cell to cell, the code runs fine.
The files can be found here: Excel to Visio - Google Drive
The current state of the files will error out, as I have left row 19 blank. To see the file work, simply copy the contents of row 18 or 20, to row 19. Thanks for any assistance here.
- Take entries from a spreadsheet and populate Visio Shapes in a specific x,y location
- Then connect the shapes with dynamic connectors based on info filled out by the user
After the shapes have been dropped into Visio, I then look at columns L (source connection) and B (destination connection) to see where I should glue the ends of the dynamic connector in Visio. This does work when the data is consistent (no gaps/blanks) between the start and end of the populated range. However, if there is a blank cell/row anywhere in the middle, I get an "Unexpected end of file" error. See the following example:
In this case, after the loop is past L19, I can see by adding watches...that it cannot determine the ID values in column L and B, which is how I connect the end points of the connector to the Visio shapes.
Below is the entire module:
VBA Code:
Sub VisioNetworkAutomation()
Dim ws As Worksheet
Dim visApp As Visio.Application
Dim visDoc As Visio.Document
Dim visPage As Visio.Page
Dim visStencil As Visio.Document
Dim visShape As Visio.Shape
Dim visConnector As Visio.Shape
Dim boundingBox As Object
Dim numberOfPages As Long
Dim i As Long
Dim deviceCountColumn As Long
Dim lastDeviceRow As Long
Dim endCell As Range
Set ws = ThisWorkbook.Worksheets("Network Architecture") ' Set your worksheet and the column number you want to check
numberOfPages = ws.Range("B9") ' Determines number of pages entered by user
' Create a new Visio document
Set visApp = New Visio.Application
Set visDoc = visApp.Documents.Open("C:\_EAA Network Automation\EAA Network_Blank.vsdm")
For i = 1 To numberOfPages
' Create Visio pages, then sets the name and background
Set visPage = visDoc.Pages.Add
visPage.Name = "Page-" & i
visPage.BackPage = "Background-1"
Next i
Set visStencil = visApp.Documents.OpenEx("EAA.VSSX", visOpenDocked) ' Open the Basic Shapes stencil
deviceCountColumn = 10 ' Column to check = J, Change this to the desired column number (1 for Column A, 2 for Column B, etc.)
lastDeviceRow = ws.Cells(ws.Rows.Count, deviceCountColumn).End(xlUp).Row ' Find the last non-empty row in the column
Set endCell = ws.Cells(lastDeviceRow, deviceCountColumn) ' Set end cell ranges
Dim dataRange As Range
Dim cell As Range
Dim xCoord As Variant
Dim yCoord As Variant
Dim connectionTemplate
Dim idMatch As Range
Set dataRange = ws.Range(Range("J12"), Range(endCell.Address(0, 0))) 'Sets the range from J12 to last populated cell in J column
' Loop through the range and store the cell values in the array
For Each cell In dataRange
connectionTemplate = cell.Value ' Grabs the Connection Template of the current cell row
xCoord = cell.Offset(0, 4).Value ' Grabs the x-coordinate of the current cell row
yCoord = cell.Offset(0, 5).Value ' Grabs the y-coordinate of the current cell row
If IsEmpty(cell) = False Then
Set visShape = visDoc.Pages("Page-" & cell.Offset(0, 3).Value).Drop(visStencil.Masters(connectionTemplate), xCoord, yCoord) ' Drops current shape on current page
visShape.Name = cell.Offset(0, -8).Value
' Write into Shapesheet data of shape
visShape.Cells("Prop.Name.Value").FormulaU = Chr(34) & cell.Offset(0, -6) & Chr(34) 'Writes C4 to shapedata
visShape.Cells("Prop.Model.Value").FormulaU = Chr(34) & cell.Offset(0, -5) & Chr(34) 'Writes D4 to shapedata
visShape.Cells("Prop.IP.Value").FormulaU = Chr(34) & cell.Offset(0, -4) & Chr(34) 'Writes E4 to shapedata
visShape.Cells("Prop.Subnet.Value").FormulaU = Chr(34) & cell.Offset(0, -3) & Chr(34) 'Writes F4 to shapedata
visShape.Cells("Prop.Gateway.Value").FormulaU = Chr(34) & cell.Offset(0, -2) & Chr(34) 'Writes G4 to shapedata
End If
Next cell
Set idRange = Range("L12:L51")
For Each cell In idRange.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers)
Set visConnector = visDoc.Pages("Page-" & cell.Offset(0, 1).Value).Drop(visApp.ConnectorToolDataObject, 0, 0)
' Connect the shapes (ID to Source)
visConnector.Cells("BeginX").GlueTo visDoc.Pages("Page-" & cell.Offset(0, 4).Value).Shapes(cell.Offset(0, -10).Value).Cells("Connections.Left")
visConnector.Cells("EndX").GlueTo visDoc.Pages("Page-" & cell.Offset(0, 1).Value).Shapes(cell.Value).Cells("Connections.Right")
Next cell
End SubThe piece in question is the last For loop which checks all populated cells in idRange (column L):
VBA Code:
Set idRange = Range("L12:L51")
For Each cell In idRange.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers)
Set visConnector = visDoc.Pages("Page-" & cell.Offset(0, 1).Value).Drop(visApp.ConnectorToolDataObject, 0, 0)
' Connect the shapes (ID to Source)
visConnector.Cells("BeginX").GlueTo visDoc.Pages("Page-" & cell.Offset(0, 4).Value).Shapes(cell.Offset(0, -10).Value).Cells("Connections.Left")
visConnector.Cells("EndX").GlueTo visDoc.Pages("Page-" & cell.Offset(0, 1).Value).Shapes(cell.Value).Cells("Connections.Right")
Next cellYou can see from the watches, when the loop is currently is on L18, it can still properly determine the ID values in L18/B18.
For whatever reason I cannot graps, after I loop past a blank in column L, it does not seem to be able to execute this to what I would expect.
At this point, it can identify L20 = 7, but then grabs the "dynamic connector" for B20?
Again, when there are no breaks in data from cell to cell, the code runs fine.
The files can be found here: Excel to Visio - Google Drive
The current state of the files will error out, as I have left row 19 blank. To see the file work, simply copy the contents of row 18 or 20, to row 19. Thanks for any assistance here.
