Using Excel to generate a Visio diagram - using 'select' method in IF statement

Question

I am currently integrating Excel with Visio to auto-populate some elements diagrammatically. I have got so far with this effort but have got stuck between the documentation, macro recordings and actually doing what I want.

By recording macros I have found a way of selecting individual elements like so: ActiveWindow.Select Application.ActiveWindow.Page.Shapes.ItemFromID(216), visSelect

I have a loop in my script as follows:

For Each oItem In vsoDocument.Pages(sitePage).Shapes
    If oItem.Name <> "Sheet.2" Then
        'vsoDocument.Pages(sitePage).Select oItem.Item, visSelect ' NOW REMOVED
        oItem.DeleteEx (visDeleteNormal)
        Debug.Print oItem.Name
    End If
Next oItem

UPDATE: Following from your responses and sanity checks you are right that I don't need to select the shape. I have amended the code to simply show oItem.DeleteEx (visDeleteNormal) now and this works as expected. However I am left with a number of shapes on my sheet, such as a shape with the name "Ethernet.46". I don't know why some shapes were deleted and also why the page background was removed. I've found if I run my For loop once, add the background back in WITH vsoDocument.Pages(sitePage).BackPage, then run my For loop again another 5 times in succession it finally removes all shapes.

QUESTION: Why is my For loop behaving unreliably?

Visio object model dox: https://msdn.microsoft.com/en-us/library/office/ff765377.aspx — David Zemens, Feb 22 '17 at 15:08
This is a shot in the dark, and maybe a little clunky, but try: `vsoDocument.Application.ActiveWindow.Select oItem, visSelect` — David Zemens, Feb 22 '17 at 15:09
Also, it's not clear from the code you've provided: why do you need to `Select` the shape? Usually this is not necessary... — David Zemens, Feb 22 '17 at 15:11
when you edit a question, especially when you edit it in response to a comment or answer, you should probably mention/tag the person who answered already, otherwise we've no way of knowing you've made the revision. — David Zemens, Feb 23 '17 at 16:56

David Zemens · Accepted Answer · 2017-02-23T17:00:26.280

the .Select line fails, and I'm pretty sure it's the oItem.Item call that's failing as

The Select line fails because the Page object doesn't have a Select method. That pertains to the Window object. From the dox, it looks like oItem.Item will also fail, so you can probably just use the oItem which itself is a Shape object.

What is the correct syntax for this command in this loop?

Possibly as follows:

vsoDocument.Application.ActiveWindow.Select oItem, visSelect

However, it's not clear from the code you've provided: why do you need to Select the shape? Usually this is not necessary. It may be possible to do what you're trying to do without relying on Select at all.

Is there a way I could have fished for this information myself? e.g. can I reveal methods associated with the oItem definition, like "Name"?

You can view the properties associated with each object using the Locals pane in the VBE, and you can view properties & methods from either the documentation (which usually includes syntax examples & use) or from within the VBE press F2 to bring up the Object Browser.

You can browse the Visio object model, here:

http://msdn.microsoft.com/en-us/library/office/ff765377.aspx

Navigating that hierarchy, you can browse the events, properties and methods of the Shape object:

https://msdn.microsoft.com/en-us/library/office/ff768546.aspx

Update

ALWAYS when you are deleting elements from a collection, you must do this by index, and in reverse order.

Dim i as Long
For i = vsoDocument.Pages(sitePage).Shapes.Count to 1 Step - 1
    Set oItem = vsoDocument.Pages(sitePage).Shapes(i)
    If oItem.Name <> "Sheet.2" Then
        Debug.Print oItem.Name
        oItem.DeleteEx (visDeleteNormal)
    End If
Next oItem

The reason this is necessary, is that when you Delete an item, the collection re-indexes itself, so when you delete Item #1, then Item #2 becomes the first index, but your loop takes you to the next item, which is now Item #3. Forcing the loop by index, and in reverse order, prevents this undesired behavior.

Using Excel to generate a Visio diagram - using 'select' method in IF statement

1 Answers1