2

I want to model something similar to a (hyper-)graph in MS Visio 2016 Professional and then export the data of the shapes to csv to further work with it.

I am trying to make a VBA Script that goes through all the shapes on the sheet and writes the (manually inserted) data from the shapes to one csv file (and in the future maybe different csv files depending on the type of the shape).

To get a feeling for VBA I tried to start with a script that counts all the shapes on the sheet but I already failed on that. Please consider this is my first time working with VBA:

Sub countShapes()
Dim shp As Shape
Dim count As Integer
count = 0
Debug.Print count

For Each shp In ActiveSheet.Shapes
count = count + 1
Debug.Print count
Next

End Sub

This returns runtime error 424, object not found.

What am I missing?

As a second step, I want the script to check that shapes that have for example the same number in the data field "id" are identical in all other data fields as well and show an error if not (before exporting to the csv files). Can I realize this using vba in visio?

Thanks a lot for any help!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ksbawpn
  • 302
  • 4
  • 19

1 Answers1

4

ActiveSheet is an Excel property. I think you're looking for ActivePage, which is a Visio equivilent. So to fix your code above you could use this:

For Each shp In ActivePage.Shapes
    count = count + 1
    Debug.Print count
Next

However, if you're simply after the shape count for a page then you could write this instead:

Debug.Print ActivePage.Shapes.Count

Can I recommend some links that might also help:

As an alternative approach you might also be interested in Visio's built-in reporting tool:

Re the second part of your question (check data fields) I'm assuming you're talking about reading Shape Data. If that's the case you first want to check if a row named "ID" exists and, if it does, read that value. So something like this might get you going:

Public Sub TestGetCellValues()
    GetShapesCellValues ActivePage, "Prop.ID"
End Sub

Public Sub GetShapesCellValues(targetPage As Visio.Page, targetCellName As String)
Dim shp As Visio.Shape
If Not targetPage Is Nothing Then
    For Each shp In targetPage.Shapes
        If shp.CellExistsU(targetCellName, 0) = True Then
            Debug.Print shp.NameID & "!" _
                & targetCellName & " = " _
                & shp.CellsU(targetCellName).ResultIU
        End If
    Next shp
End If
End Sub

...which might output something like this (given the associated shapes):

Sheet.2!Prop.ID = 3
JohnGoldsmith
  • 2,638
  • 14
  • 26
  • Thanks a lot for your reply! I looked into the resources you provided and they seem very helpful. I saved them for future reference. Could you maybe further clarify the second part of the answer? I tried your code and it does indeed print all the "ID" Values of my shapes. Now I want to print String values saved as "Type". When I just change the targetCellName to Prop.Type it does work but only prints "0". Do I have to call another method instead of `shp.CellsU(targetCellName).ResultIU` ? – ksbawpn Jun 30 '18 at 17:04
  • You have a row called "Prop.Type", is that right? If you look at the ShapeSheet, what is the formula and value currently (you can toggle between Formulas and Values view with F5)? (ie what are you expecting it to be?) – JohnGoldsmith Jul 02 '18 at 14:28
  • Both Formula and Value show "Type". `Public Sub TestGetCellValues() printShapeCellValues ActivePage, "Prop.Type" End Sub Sub printShapeCellValues(targetPage As Visio.Page, targetCellName As String) Dim shp As Visio.Shape For Each shp In targetPage.Shapes If shp.CellExistsU(targetCellName, 0) = True Then Debug.Print shp.CellsU(targetCellName).ResultIU End If Next shp End Sub` If I use this code I expect it to print the String values in the shapes row "Prop.Type" to the console. Hoever I only get 0s. – ksbawpn Jul 03 '18 at 14:07
  • 1
    Ok, I see. Cell has a number of result properties that you can use to get the result you're after. The one above is `ResultIU`, which is 'internal units' however, for a string you could use `ResultStrU("")` Have a look at this link for more details: https://msdn.microsoft.com/en-us/vba/visio-vba/articles/cell-resultstru-property-visio – JohnGoldsmith Jul 03 '18 at 17:24
  • Thanks a lot! I came across the `ResultStrU` method earlier but could not get it to work because I did not use an argument. Now it works as expected! – ksbawpn Jul 03 '18 at 17:39
  • Great. Glad you're all fixed. – JohnGoldsmith Jul 03 '18 at 17:59