Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •