Visio import of Excel data (Excel 97 SR2 WinNT Visio 2002)

Topic

New Reply

Having failed to solve the problem in Visio and VB lounges I’ve come to the middle man (If you guys can solve it then no doubt I’ll get the smug ” should’ve come here in the first place”). On the other hand, if you do solve it then you get the kudos.
Problem: visio 2002 has flowchart wizard. It imports data from excel file which contains 3 worksheets – 1st sheet has shapes details, 2nd has connectors details, 3rd contains just one cell with template name in it.
When the data is imported, it doesn’t seem to arrive in any certain order, and when it is reformatted using “For each shape in activepage.shapes……next” there is no logic behind which shape is chosen first nor in which order the shapes are addressed.
With all other avenues empty I was wondering if the Excel gurus had any suggestion as to how the VBA addresses the For…each…next scenario when examining worksheet objects.
FYI there’s a bit of a more detailed post in the visio lounge.
TIA
Alan
Cheshire
UK

Reply | Quote

Replies

Instead of ” for each”
use ” for x = 1 to shapes.count”
then get “shapes(x).name”, or whatever, etc
Or put all the names in an array and use the names to refer to them

Steve

Reply | Quote

Thanks Steve, fresh pair of eyes for me please.
The “for x = 1 to shapes.count” didn’t make any difference but it did prompt me to get the shape name; extract the number part of the name (rightmost end characters); and then place the shape name in an array at the corresponding value position ak la:
For i = 1 to activepage.shapes.count()
Set objcurrent = activepage.shapes(i)
ShapeNumber = objCurrent.name
ShapeNumber = Right(ShapeNumber, Len(ShapeNumber) – 3) ‘the shape name is same format as Pro76, Pro77 etcetera
ShapeNumber = Cdbl(ShapeNumber)
ShapeArray(ShapeNumber) = Shape.name
Next

Then I can address the shape names in order by because shape named Pro76 is at the array position 76 and Pro77 is at array position 77 etcetera.:

For i = 1 to 100
Set Objcurrent = ActivePage.Shapes(ShapeArray(i))
‘do other code to shape like start aligning and spacing from left
Next

Thanks again Steve.

Alan
Cheshire
UK

ps. anyone got any good recommendations on Visio VBA books.

Reply | Quote