I know that I have seen it before but can't find it.

I have a report - exported to Excel.

Then I want to change the report in Excel (because it's easier than to modify every shape in Visio) - how I can replace shapesheet-values by the changes I made in Excel
In other words: read back from Excel-cells to Visio-Shapesheet-Cells?

And how to ensure that the format is the same as required in Visio (e.g. Lists)?

I stand on the tube - Ich steh auf'm Schlauch

Thanks for links and tips!
hl

Might this be the elusive post:  https://visguy.com/vgforum/index.php?topic=7302.0

Hi, I have two related contributions:

https://youtu.be/LQpllJpzsU4
https://visguy.com/vgforum/index.php?topic=7581.0

https://youtu.be/OYpENi7Pg4U
https://visguy.com/vgforum/index.php?topic=7572.0

The first tool has explicit support for fixed list, variable list and boolean data types.

Visio has native shape data support.

I think you can check out this educational video from David Parker and Chris Roth (aka "Visio Guy") series:
https://www.youtube.com/watch?v=ckZs4B8tS_I

The idea behind Visio shape data linking is, you edit your data in Excel (or other "data source"), and then use it in Visio.
You do not edit it in Visio, just use. Therefore, lists and stuff like that is not needed (and not supported)

Editing shape data in Visio has lots of issues:
- The person who edits data, needs Visio. This may be a problem already;
- The built-in editing experience is far from perfect (if you compare with Excel for example, or specialized forms)
- Allowing to edit data in Visio AND another app would lead to "two sources of truth" and the need for synchronization, which is never a good thing

Thanks a lot!

@Nikolay - first try went totally wrong - maybe because I have different reports upon different shapes for different purpose (terminals, relays etc) with different props. Probably my fault

@wapperdude - looks really nice! I have to test it after modifying. I'll check how to export to a template (already open?) rather than a new file. with already defined formatting and coding. And access to all props.

@Thomas - I didn't check this out but I have to finish the schematic itself with all the lists required Monday.

It turned out that this was not the most important task- with a bit more discipline while dropping shapes could reduce this to a minimum.
Insofar reports are ok.

By the way: when defining a report - where I can set if a user.- or prop.- field/cell is selectable for the report itself (not criteria)? Some props are not in the list. Where's the gap?

have a nice weekend!
hl

Hm I don't quite get it, what went totally wrong?
I thought I have posted a link to the "Visio MVP Series" play list, "using shape data" that explains what shape data is and how to use it?

Do you mean, linking to that exported file did not work, or?

Hi Nikolay - the link works but the import "destroys" everything (at the copy). I didn't get the point which exported data "belongs" to which shape.

There are different shapes - in terms where-they-shall-be-listed.
OneD for wiring and another 2 for terminals and devices. Insofar the built-in-reports works although I didn't found some of the user.xxx and prop.xxx cells in the list.
In the picture attached I expect to have a cell "Color" but it isn't.
Can't find anything about how to get a cell within this list. What are the requirements for this very cell, y'know?

Thanks
hl

This wizard only lists the labels, not the property name.
So, check if you have something like that:
Prop.Color.Label = ""
And change it as follows:
Prop.Color.Label = "Color"
You can change that in the document stencil to upgrade all instances at once.
Can you provide an example document including the report?

Hi Thomas, you're right. I found it inbetween.
The thing ist that the tool is under construction.
The formulas or values I change with vba because actually I have different "same" master-shapes in the document's stencil.
It consists more or less on your recommendation

Sub ChangeFormula()

'https://visguy.com/vgforum/index.php?topic=6210.msg25326#msg25326
'to add a formula programatically:
'1. test the formula in the shapesheet directly
'2. copy the formula as a comment here: 'formula
'3. replace every " with <" & CHR(34) & "> (find/replace tool) in this very formula
'4. copy this to assign it to the variable and put doublequotes (") at both sides of the formula
Dim shp As Visio.Shape
Dim RName As String
    RName = "Color"
Dim FmL As String
    FmL = "=" & Chr(34) & "Color" & Chr(34)
For Each shp In ActivePage.Shapes
    If shp.CellExists("User.intType", visExistsAnywhere) Then
        If shp.Cells("User.intType").ResultStr("") = "Terminal" Then
            shp.Cells("Prop.Color.Label").FormulaForceU = FmL
            'shp.CellsSRC(visSectionProp, 0, visCustPropsValue).FormulaForceU = FmL
        End If
    End If
Next

End Sub

Hmmm looks I am still a bit confused what you mean by "import destroys everything"?

Update (import) is supposed to read the values from Excel cells and put them into "shape data" properties.
Is it not happening?

To match rows to shapes, you need to check "automatically link shapes", and select the appropriate key field.
For example, if your shapes in Excel are identified by its by shape ID (?), you need to specify ID as a key.

Quote from: Nikolay on September 15, 2024, 07:56:58 PMUpdate (import) is supposed to read the values from Excel cells and put them into "shape data" properties.

yes it does - but where Visio knows from what data row in Excel belongs to what shape in visio?
So it turns out that the connections I made Visio disconnected after import and put them somewhere else with a data field. It looks very strange and stopped inverstigations here.

Maybe I should have mentioned that not all shapes on the page does have data and different shapes have different data for different reports.
It's about an electrical schematic - wireList knows from to..., deviceList knows product, terminalList knows connected cross sections and so on.

But nevertheless: Thanks!
hl

You specify what property do you what to use as a "key" for shape when linking. For example, you can use Shape ID (every shape in Visio has unique ID). Or you can use some other property (something like equipment inventory number).

When linking to such a file, you choose what excel column (property) should be used to connect rows to shapes. It's called "key column".



Just a thought - you should use "Custom Import" button, NOT "Quick Import" button to get all import options.
The video refers to it, as "quick import" did not exist at the time when that vidoe was filmed.



Also, make sure you have automatic data graphics generation turned off, this may be confusing (i.e. this may be what you call "diagram is destroyed")