How to set the value of a shapes data

Question

Visio VBA Macro: I wish to set the value of a shapes shape data after the shape has been dropped onto the page by the program. I created a hex shape with 5 defined data shape strings.

I have tried searching for answers but have found no clear instructions on how to do this

   Dim strConnection As String
Dim strCommand As String
Dim strOfficePath As String
Dim cellString As String
Dim vsoDataRecordset As Visio.DataRecordset
Dim vsoCell As Visio.Cell

strOfficePath = Visio.Application.Path

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                   & "User ID=Admin;" _
                   & "Data Source=svs2.XLSX;" _
                   & "Mode=Read;" _
                   & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                   & "Jet OLEDB:Engine Type=34;"

strCommand = "SELECT * FROM [BaseTemplate$]"

Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Data")

MsgBox "Connected Up:" & vsoDataRecordset.DataColumns(1)

'Get the row IDs of all the rows in the data recordset
lngRowIDs = vsoDataRecordset.GetDataRowIDs("")


'Iterate through all the records in the data recordset.
For lngRow = LBound(lngRowIDs) + 1 To UBound(lngRowIDs) + 1

    varRowData = vsoDataRecordset.GetRowData(lngRow)
    'varRowData = vsoDataReco


    'Drop Shape onto page
    Set shpObject = pagThisPage.Drop(Application.Documents("microservices V2.VSS").Masters("Microservice"), 0#, 0#)

    'Update Shapes Text
    shpObject.Text = varRowData(0)

    'Get cell that i want to update
    Set vsoCell = shpObject.Cells("Prop.Object")

    'Copy data i want to apply to shape to string
    cellString = varRowData(0)

    vsoCell.Formula = cellString


    'get shape data value to print out
    dt = shpObject.Cells("Prop.Object").ResultStr("")

    MsgBox "---" & dt & ":" & vsoCell.Formula

Next lngRow

Sorry, but I can't understand you need set or get property ? — Surrogate
I have a shape (e.g. Hexagon) that has defined shape data that i added to shape. I want to add, update specific shape data values. So i want the shape data Prop.Object to have a value e.g. "Text" which is updated via the macro. — James

AJD AJD · Accepted Answer · 2018-12-20T03:03:04

Shape data is held in the ShapeSheet. You can access a ShapeSheet Cell to put information in, or you can access a ShapeSheet Cell to get information out.

Set vsoCell = shpObject.Cells("Prop.Object") - this assumes you have the appropriate property (row) called "Object" in your ShapeSheet. You also have "User." Cells to work with.

Now you can simply use your object vsoCell to fill with information (e.g. vsoCell.Formula = "Scratch.X1") - you may require double quoting to put a literal string in, instead of a formula.

Take a generic shape, add some shape data, and then open the shapesheet to see how this is stored. This will give you some ideas about what names to call and how to manipulate this data. The ShapeSheet is a very powerful part of the Visio model, and once you learn how to manage it you can do some really good stuff.

How to set the value of a shapes data

2 Answers