Simple VBA question

Started by maclarkson, September 02, 2019, 10:20:28 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

maclarkson

I tried that but the ID is the ID from the shape. The shape ID is only unique to sheet. I want the prop.objectID from the shape which is the one from the database. This one is unique

wapperdude

This is all very confusing.  A simple example would help clarify things.  Do the shpFrom and shpTo have the prop.objectID?

If so, you can assign the shapetext as explained before, to have this ID.  But, why do that?  You hide the text anyway.  If this is for a connectivity report, isn't the info in the Excel file already?  You can make a custom report, if that's the goal, just by accessing the prop.objectID value directly from the shape, and not mess around with all this shapetext stuff.

So, a picture or Visio file showing what you want, at this point, will (ought to ) clarify what you your trying to accomplish.

Visio 2019 Pro

maclarkson

I have attached an example.
If you click the report icon you will see it creates an excel report that tells you which app is connected to the other. If you copy and connect up to another object you will notice it creates a new row for each connector. It uses the shpfrom.text to grab the app text. Change this to shpfrom.id and it will use the ID. I do not want either of these. Now connect up the attached register. I want the ID that is in the Attached register to be the from and to ID's not the ID from the shape. I was then going to connect up the connectors with the sheet two so that I now have a record in the excel sheet of what is being connected to what.

wapperdude

#18
I see a major problem... the shapes, e.g., App1, do not have any shape data, certainly not Prop.ObjectID.  Why do you mention this?  It doesn't exist?

So, you have two options for your project...
A)  When the code finds a connected shape, either a From or To, then, use that shape's text to search the Excel file for matching AppName, and then grab the corresponding ObjectID.  Two more choices
      I.) take the ObjectID and stuff it directly into your new report, or
     II.) take the ObjectID and stuff it into the appropriate Prop.To or Prop.From value cell of the connector.

B) When the "App(whatever)" is added to the shape, create a Prop.ObjectID and add the correct value.  Then, when the connector finds an attached shape, check to see if Prop.ObjectID exists.  IF so, grab it's value and place into the appropriate connector value cell.  If the Prop.ObjectID doesn't exist, ignore. 

Note, once the Prop.ObjectID has been added, I believe the built-in Reports function can be configured to yield the desired info without resorting to custom code.


Visio 2019 Pro

maclarkson

Sorry I attached the wrong document

Check now you will see that I have connected up the data and it has a prop.objectID

wapperdude

#20
You need to change the two shpText lines to be as follows:
   shp.CellsU("Prop.From").FormulaU = shpFrom.CellsU("Prop.ObjectID").ResultStr(visNone)
   shp.CellsU("Prop.To").FormulaU = shpTo.CellsU("Prop.ObjectID").ResultStr(visNone)


This will put the desired values in the connector From/To value cells. 

It doesnot address changing the "App1" or "App 2" labels if desired.  See this post  http://visguy.com/vgforum/index.php?topic=7638.0

Visio 2019 Pro

maclarkson

Hay that's awesome and it worked a treat so I tried to apply it an extra row of data

shp.CellsU("Prop.FromName").FormulaU = shpFrom.CellsU("Prop._VisDM_AppName").ResultStr(visNone)
shp.CellsU("Prop.ToName").FormulaU = shpTo.CellsU("Prop._VisDM_AppName").ResultStr(visNone)

But now I get a debug error.

wapperdude

When you use the CellsU method couple of things...
1) obviously, the cell has to already exist.
2) what's inside the quotes is taken literally.  That applies to Upper/Lower case and spelling.  It literally has to be the exact name as shown in the shapesheet.
Visio 2019 Pro

maclarkson

#23
I checked the case and the spelling and for the life of me could still not find the problem.

I will remove the report function eventually and I will just want to run the code to update the cell contents. Is there line of code I can add in to trigger the update each time I change something.


wapperdude

I can confirm and reproduce the error.  So, you're not going crazy.  But, for now, I have no explanation.
Visio 2019 Pro

wapperdude

#25
This syntax works: shp.CellsU("Prop.FromName").Formula = Chr(34) & shpFrom.CellsU("Prop._VisDM_AppName").ResultStr(visNone) & Chr(34)

Visio 2019 Pro

maclarkson

Awesome! By the way? what does the chr(34) do?

wapperdude

Chr(34) is the dbl quote.  But, avoids having to use many, multiple dbl quotes in a row.

I believe Visio Guy had an article about it on his website.
Visio 2019 Pro