Create a drawing from X and Y values in Excel

[LargeMike]

I need help. I am new to Visio VBA, and to VBA in general. I know enough to get in trouble, but not to get out again!

I am trying to create a drawing using data stored in Excel. I have X and Y values in that should correspond to coordinates on the drawing.  All shapes will be identical, they just need to be positioned according to the coordinates in the excel file.

The number of rows in the excel file will vary.

I'm not sure how to get Visio and Excel to communicate. I would appreciate any help and suggestions.

Thanks

[Yacine]

Hi Mike,
this problem reminds me very much of this one.
It works with Visio 2007 and newer versions.
As preparation, set up a shape that has it's pinx and piny depending on custom properties...
Then linking to data: Menu Data / Link Data to Shapes / ... select your excel file ... either selected individually shapes or drop your new one from a stencil.

Cheers
Yacine
---- a second, more attentive reading:
you should not need any VBA, unless you really want it.

[LargeMike]

That's great and works like a charm in Visio 2007.  Unfortunately I am working with Visio 2003, and I can't seem to find a similar function.

If you have any other ideas, I'd love to hear them.

[Yacine]

Hi Mike,
with 2003 it is the same idea, namely to bind the position of your shapes to custom properties.

The binding to a database is not so comfortable. 2 solutions: "database wizard" or accessing your database by hand err... VBA.
I never liked the wizard, it did not always do what I wanted. In VBA you have the handling in your hand, but I don't know how good you are in VBA.
I'll try to find some useful links, unless you tell that it is not a problem for you.

---- 11.06. ----
Here is a snippet for an access database ... shall find one for excel too.

Code

Sub GetData()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Const DBFullName = "... your file name here..." 'you could add a search dialog here to look for the DB
Const TableName = "TheTable"
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    Set rs = New ADODB.Recordset

rs.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
rs.MoveFirst

For i = 1 To rs.RecordCount
    'here you would drop a shape on your drawing...then
    MyShp.Cells("pinX") = rs!TheXValue
    MyShp.Cells("pinY") = rs!TheYValue
    MyShp.Cells("fillforegnd") = rs!TheColourValue
    rs.MoveNext
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

--- 15.06. ---
And here is the excel equivalent... http://support.microsoft.com/?scid=kb%3Ben-us%3B257819&x=14&y=12