Importing a list of XY Coordinates: Selected Shape is Duplicated at each XY

[Bacon_and_Scotch]

I've seen a few variations on this topic, but haven't yet found exactly what I'm looking for.

I'm hoping to be able to select a specific shape (say a circle or square) and then import a list of XY coordinates from Excel such that a copy of this shape will be made for each row of data and at the X & Y coordinates specified.

I'm attaching a Visio file of a manual effort where I'm trying to make a template for use with my Bosch 1400 POF router (lovely router, but not compatible with any of the circle jigs out there).

For now, because I'm not sure how else to do it, I'm manually copying and pasting the XY coordinates into each shape from Excel.  Literally, I'll make 42 copies of a specific shape and then, one by one, copy and paste the X and Y values from Excel.  -Two monitors, patience and robust Ctrl-C & Ctrl-V fingers are helpful here.  The attached Excel worksheet also shows some example of the data I'm hoping to import.

Thanks in advance for your patience if this has been covered elsewhere and any assistance you may offer.

[Surrogate]

Which version of Visio do you use: Standard/Professional/Plan2 ?

[Bacon_and_Scotch]

Hi, yes I might have mentioned that.

I'm on Plan 2 (O365 corporate version).

Thanks a lot for taking the time to respond.

[Bacon_and_Scotch]

In case this is of any help, I found this gentleman's work where he's set things up so that points can be added to a map.  However, I'm not skilled enough in Visio or VB coding to make use of his work for my purposes:

https://bvisual.net/2015/05/27/distributing-data-points-automatically-on-maps-in-visio/

From what I can see, it's necessary for his use case to scale everthing in relation to the map and he adds 2 fixed coordinates "LL Marker" shapes as reference points from which all new shapes and the coordinates are based off of.  -For my use case, I don't believe this is necessary.  I will typically set the ruler / grid origin to the centre of whatever page I'm working on.  For example, if I'm using A4 in landsape, then I set the grid & ruler orgin as Horizontal = 148.5 mm, Vertical = 105 mm and that's pretty much the only reference that matters for my use case.

I hope this doesn't go against the spirit of the letter of the forum rules in any way, but I'm not above buying beers or coffee for anybody who can render assistance with this.  That said, if it does go against the rules, then please note that I must remain in compliance and will retract and refrain from this offer and such offers in future.

Thank you for you understanding and patience.

[Surrogate]

if you modify your masters in document, and use data linking with excel workbook you can make automation without VBA codding.
I have Visio Standard (this edition dont support data linking) with russian interface, for this reason cant explain to you how create data linking for your purpose 

[Surrogate]

For me simpliest way write about 10 lines of code

Code Select Expand

Sub bb()
Dim ea As Object, ew As Object, x As Single, y As Single, es As Object, er As Object
Set ea = GetObject(, "excel.application") ' define excel app
Set ew = ea.activeworkbook ' define active workbook
Set es = ew.sheets(1) ' define active sheet
Set er = es.usedrange  ' define used range
For r = 2 To er.rows.Count ' iterate rows from 2 to end
x = es.Cells(r, 1) / 25.4 ' get x value in inches
y = es.Cells(r, 2) / 25.4 ' get x value in inches
ActivePage.Drop ActiveDocument.Masters(4), x, y ' drop shape from document stencil
Next
End Sub
PS Changed variable type for X, Y.
In attached document i create new master-shape
ActivePage.Drop ActiveDocument.Masters(4)

[Bacon_and_Scotch]

Hi Surrogate,

That looks great!  Thank you!!

Small and (feeling quite stupid to ask) question: 

How does it get the data from Excel? 
-Do you need to somehow import the data from Excel into Visio?
-Does the macro import the data from the whichever active Excel speadsheet I happen to have open on my desktop?

I can see that this is probably so basic and fundamental that it didn't seem necessary to spell it out.  However, I'm afraid that's where I'm starting from.

Thanks again for your patience and support!

[Bacon_and_Scotch]

Hi Surrogate,

I think I've answered my own question.

It grabs the data from the active Excel Workbook / Worksheet that I have open.  This is perfect for me, BTW.

Thank you so much!! :-)

[Bacon_and_Scotch]

One thing I'm trying to work out:

My default scenario is working in Metric, A4 in Landscape mode.  The Ruler & Grid are set such that Ruler zero (Horizontal) = 148.5mm and Ruler zero (Vertical) = 105 mm.  Same is true for Grid origin.

When I run the macro, I notice that each of the objects X & Y values are offset precisely by 148.5mm on the horizontal and 105mm on the vertical.  At this point, it's a trivial matter to correct for this behaviour in Excel by transposing each of the coordinates within Excel.  (Simply add 148.5 to each X value and 105 to each Y value).

However, I suspect it may be better this way:

I've modified the Macro code as:

Sub bb()
Dim ea As Object, ew As Object, x As Single, y As Single, es As Object, er As Object
Set ea = GetObject(, "excel.application") ' define excel app
Set ew = ea.activeworkbook ' define active workbook
Set es = ew.sheets(1) ' define active sheet
Set er = es.usedrange  ' define used range
For r = 2 To er.rows.Count ' iterate rows from 2 to end
x = es.Cells(r, 1) / 25.4 + (148.5 / 25.4) ' get x value in inches
y = es.Cells(r, 2) / 25.4 + (105 / 25.4) ' get x value in inches
ActivePage.Drop ActiveDocument.Masters(4), x, y ' drop shape from document stencil
Next
End Sub

It seems to work.  I'm hoping this is the way to do it.

Thank you again for your support and patience!

[Thomas Winkel]

Hi,

interesting exercise for Addin and setup programming.
After installation there should be a new Ribbon "Tools" with two buttons in Visio.

Button "Modify":
Exports a list with positions of selected shapes.
Let you modify the positions directly live in Excel.
Best view Excel and Visio on different screens to see the results.

Button "Duplicate":
Opens an empty list of positions.
Type as many x/y positions as required.
For each position a copy of the primary item in current selection will be created on Excel before close event.

Both Buttons will only do something if at least one shape is selected.

I used Nikolays Extended Visio Addin Project:
https://unmanagedvisio.com/tools/visual-studio-project-templates-to-create-visio-add-ins/

Code is on Github:
https://github.com/ThomasWinkel/VisioShapePosition

Installer attached.

@B&S:
You can read the current ruler offset in page shapesheet cells "XRulerOrigin" & "YRulerOrigin" to make the code more generic.

BR,
Thomas

[Bacon_and_Scotch]

Hi Thomas,

I'm fairly blown away, really!!  Great work! :-)

I'm still kicking the tires and have been slowed down a bit after a +ve COVID test. :-(    -At least it will be over for me when it's over...

Just wanted to say that I'm really grateful and appreciative of your work!

[Thomas Winkel]

Oh, I hope you ride it out without complication!

I updated the installer above and the code on Github.
Now it also handles ruler offset.

Sometimes the Addin looses the connection to the excel object.
I cannot reproduce it.
Maybe someone has an idea what could be the reason and how to fix it?

I just noticed that MS defender blocks the installation because the code is not signed with a proper certificate.
In that case you can continue by clicking on "More info".