using linked data to populate text boxes [solved]

[M.G.Ford]

Hi Guys,

I have spent the last hours trying to find an answer online to no avail. I have created many custom shapes. I am pulling a dataset from a database. I then use that recordset to drop appropriate shapes and link them to a row in the recordset. This is done in a loop

Code Select Expand


Set vsoShape = ActivePage.DropLinked(Visio.Documents(varRowData(9) + ".vss").Masters(varRowData(10)), _
                                            dblX, _
                                            dblY, _
                                            vsoRS_VisioShapes_ID, _
                                            lngRowIDs(lngRow - 1), _
                                            False)


Visio nicely creates a datasheet of the results on sheet.1! (I think that it is always sheet.1). What I want to do is to get the values from the datasheet into the textboxes in the shape. in the shapesheet, Visio adds Prop._VisDM_<database field>. I have grouped all the fields that I need to supply data to in a group named "updateables". I want to do something like

Code Select Expand

<within in the shapesheet of the textbox>User.netZone = sheet.1!prop._VisDM_net_zone

the problem is that the sheet.x of the text box is unpredictable. sometimes it is sheet.7!prop.netZone, sometimes it is sheet.8!prop.netZone. I see two options that might solve my problem

1. be able to discover the sheet.X that the textbox has then I can reference it directly
2. rename all the sheet.X within all my shapes to something consistent then I can code for that.

I have tried to ungroup and put custom prop. values but they get deleted and the new ones are added. There was a post here about using the format menu to change the sheet.X value but I don't see the format on the ribbon bar.

maybe there is a better way altogether? I have had to teach myself VBA in visio so I expect there are some tricks that I am missing. I guess the real question is: "What is the best way to display the data from a recordset in a custom shape"

Thanks for any help or direction with which to search.

Michael.

[M.G.Ford]

Continuing to search for answers... does each textbox have to have a user.<thing> = prop.<thing>? if I:

1. right-click textbox
2. data > define shape data
3. enter the name I want to use
4. save

then look at the shapesheet, both user and shape-data has been added. I know that I could use a data graphic but I don't want to side pop out... I want it part of the original shape.

[aledlund]

you might try this (suggest you turn on macro recorder to capture the code).
select your shape
toolbar > insert
insert > field
select shapedata > select custom shapedata field you want to add as text
click ok

hth,
al edlund

[M.G.Ford]

thank you for your suggestion. here is what I think that I am going to do.

Code Select Expand


    For countx = 1 To vsoShape.Shapes.Count
        If vsoShape.Shapes.Item(countx).Name = "updateables" Then
            For county = 1 To vsoShape.Shapes.Item(countx).Shapes.Count
                Debug.Print tempString & "; " & vsoShape.Shapes.Item(countx).Name; "; " & vsoShape.Shapes.Item(countx).Shapes(county).Name
            Next
        End If
    Next


using this, I can check the shape name and group. if the shape name and group matches, I can do something like

Code Select Expand

vsoShape.Shapes.Item(countx).Shapes(county).formula = "sheet.1!prop._VisDM_<database column>

I will wrap this in a function so that I can just pass in the shape and textbox I am looking for then update the formula. if I get this solution working, I will post and update as an answer to my question so that it might help others. I am finding the VBA tools in Visio a bit sparse. Anyway, as always, thank you all. I am still open to suggestions from everyone . I tend to be less elegant that I would like in an effort to get stuff done.

Michael.

Michael.

[M.G.Ford]

here is what I came up with and it works for what I need. I does feel cumbersome, though

Code Select Expand


Public Sub shape_data(subShape As Shape, searchGroup As String, searchItem As String, cell As String, userCell As String)
    Dim countx As Integer
    Dim county As Integer
   
    For countx = 1 To subShape.Shapes.Count
        If subShape.Shapes.Item(countx).Name = searchGroup Then
            For county = 1 To subShape.Shapes.Item(countx).Shapes.Count
                If subShape.Shapes.Item(countx).Shapes(county).Name = searchItem Then
                    subShape.Shapes.Item(countx).Shapes(county).CellsU("user." & userCell).Formula = "sheet.1!Prop._VisDM_" & cell
                End If
            Next
        End If
    Next
End Sub


the way that I use it is that once the shape is dropped, I update the data formulas before dropping the next shape. The are some assumptions that I make here.

1. part of the custom shape creation was to add a user.cell to the text boxes and since I named the cell, I know what to look for
2. you have grouped all the text boxes into their own group

[Yacine]

Hi Michael,
Am I right that you are trying to make a table of the data in your shapes?
It would be easier to work with a report.