Help to Connect Shapes!

[Amy]

Hey everybody!

In brief this is my problem:
I have an excel sheet with column information:
A                    B
box1              box5
box2              box6
box3              box7

I also have a visio which contains the text/content as box1 within a shape . I want an arrow connector pointing from the shape containing the content box5 to box1.

Is there anyway anyone could spare sometime to look at this problem?

Thank you for your time on this!

Regards,
Amy

[Amy]

Adding some more information


1) Initially, Excel data headers are as below:


A1: Name
B1: Description
C1: Days
D1: Times
E1: Timezone


2) Starting from second row, for each row of data, a visio shape should be generated.
3) This is how the visio backend code of the required shape look like

'Main outer rectangle/master container
Dim MasterContainerShp As Visio.Shape
Set MasterContainerShp = ActivePage.DrawRectangle(2, 1, 8, 4)


Dim DescriptionBoxShp As Visio.Shape
Set DescriptionBoxShp = ActivePage.DrawRectangle(2, 3.5, 8, 2)

Dim JobnameShp As Visio.Shape
Set JobnameShp = ActivePage.DrawRectangle(2, 3.5, 8, 4)


Dim JobDaysOfExecution As Visio.Shape
Set JobDaysOfExecution = ActivePage.DrawRectangle(2, 1.5, 4, 2)

Dim JobTimingShp As Visio.Shape
Set JobTimingShp = ActivePage.DrawRectangle(4, 1.5, 6, 2)

Dim JobTimeZone As Visio.Shape
Set JobTimeZone = ActivePage.DrawRectangle(6, 1.5, 8, 2)


4) This is exactly how the shape should be. There is an outer rectangle within which there are subshapes
5) From second row, value in column 'A' should go as content in JobnameShp
Value in column B goes to DescriptionBoxShp
value in column c goes to JobDaysOfExecution
value in D goes to JobTimingShp and offcourse E column data goes to JobTimeZone


6) Similar process to be repeated for all rows in excel.
7) All visio figures needed on the same page in a nonoverlapping fashion.



Now, I have created shapes with the required content.

I dont know if there is a better way than the above code to create those shapes. However, my next step would be to connect these shapes I have created.

Again, the sample data on how to establish connections is given in my first post.

whatever is in column B should be connected to column A (one by one)
If at all anytext is not found in visio ( what we look for in column A and column B is not found on searching the text present in JobnameShp of all shapes in visio doc) then skip this and move to the next row in excel.

Can somebody please help? Thanks so much!!

[Amy]

Can anyone please provide asome direction here..

atleast some point to start off

[wapperdude]

For starters, use the macro recorder.

You already have the shapes, select them, use recorder to get grouping syntax.  Alternatively, you could modify the following code from the MSDN library https://msdn.microsoft.com/en-us/library/office/ff767024.aspx

Code

Public Sub Group_Example() 
 
 Dim vsoShape1 As Visio.Shape 
 Dim vsoShape2 As Visio.Shape 
 Dim vsoGroupShape As Visio.Shape 
 Dim vsoSelection As Visio.Selection 
 
 'Draw two rectangles. 
 Set vsoShape1 = ActivePage.DrawRectangle(1, 2, 2, 1) 
 Set vsoShape2 = ActivePage.DrawRectangle(1, 4, 2, 3) 
 
 'Deselect all shapes, and then select the two rectangles. 
 Set vsoSelection = ActiveWindow.Selection 
 vsoSelection.Select vsoShape1, visDeselectAll + visSelect 
 vsoSelection.Select vsoShape2, visSelect 
 
 'Group the rectangles into a group shape. 
 Set vsoGroupShape = vsoSelection.Group 
 
End Sub 

Then, duplicate the group, place it conveniently, start recorder, add connector. Gives you the necessary syntax.  Again, you might want to check this:  https://msdn.microsoft.com/en-us/library/office/ff765915.aspx

HTH
Wapperdude

[Amy]

Thank you so much for your time on this!

If its not too much to ask,

1) Do you think this is the optimal way to crate shapes? Do you think theres a better way? ( There are 500 to1000 box shapes to be created)

2) Do you think it is possible to read a boxname from excel, do a find on a visio page and then keeping that in memory ( or using some other way) search for the connecting box text and then finally establish a pointing arrow connector?

3) How could i possibly relate a shape and text?

I would record the macro as you have instructed and let you know if Im able to answer those myself/ figure out anything at all..

Thank yoU!! Much appreciated!

[Amy]

Sorry, but one more question..

Is there any way I can connect shapes using arrowpointed connectors, if I know the shape ids of boxes I need to connect??
Can I select a shape if I know its shape id and alter the piece of code / logic you have provided here..

Please help me if possible.
SOrry if these are dumb questions

Thanks again!

[wapperdude]

Two approaches...Google search something like Visio vba change dynamic connector ends.

Or, use macro recorder...
Place a connector manually, start recorder, select connector and change endpoints, stop recorder.  Use <alt>+ f11 to access VBA window.  Look at recorder results, copy lines pertaining to endpoint changes.

Now, in your macro, after line that places the connector, paste your copied lines. Note, the connector should still be selected are this point.  There may be a few tweaks to the code, but should be close.

Wapperdude