News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Refreshing Excel Linked Data Corrupts Data

Started by axattenb, September 01, 2020, 07:20:04 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

axattenb

I've been using the "Shape Reports" and "Custom Import" tools to create a link between excel worksheets and a visio drawing.  My drawing has some shapes which are valves and some which are equipment, and each valve or equipment item has a TAG (unique).  Each shape has a user-defined cell "User.CPMSetList", which has a value of "Equipment" or "Valve" as approrpiate, and this cell is used to generate the corect excel worksheet (valve list or equipment list).  I then copy the excel generated lists to another workbook (which has formulae for calculating the size of the valve and the properties of the pump etc).  I used "Link Data" to link the excel and the visio shape data.

For a while, everything works fine.  If I modify the shape data in visio and then "Refresh All", the excel data is updated and if I update the excel data and "Refresh All" the visio shape data is updated.  This is exactly what I want.

HOWEVER!  At a certain point in time, the updating goes crazy.  All of a sudden, my valve TAG has changed to an Equipment TAG, and the valve shape now has shape data appropriate for Equipment and not for a Valve.

I've tried re-creating the drawing.  I've tried it using Visios Process Engineering set of tools, and I've also tried to re-create the same functionality without using the Process Engineering set of tools.  But eventually, whatever I do, I hit the same problem.

I'm wondering if maybe I accidentally duplicate a TAG and that sends the whole thing crazy?  I'm not sure that I do, but I really don't know where to go next.

PS.  I actually have 5 lists: Equipment; Valve; Motor; Instrument; Pipeline

wapperdude

Is it just the updating from Excel that goes bad?

Is it just the Valve that gets replaced by Equipment data?  And, if so, is it all Valve shapes?

Visio 2019 Pro

axattenb

I'm pretty sure the problem only happens following an Excel update.  It does not happen to all valves, it happens to random shapes in different categories (I have valves, instruments, pipes, equipment & motors) and I'm struggling to make out a pattern.

Do you think it would help to create a Unique ID from within visio  for each shape, and then use that as the look-up key?  And how would I do that?

Nikolay

#3
I have made a sync tool a few years ago. Maybe you could find it useful.
At least it shows you all pending changes (per property, per shape) and allows you to review and confirm/reject them before breaking stuff and committing them.

https://unmanagedvisio.com/products/visio-back-sync/

Should be compatible with "shape reports" + "custom import" approach (no additional configuration required).
If you think it could be useful in your scenario, but something does not work for you please kindly report.


Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 198 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1064KB.
Tokens: post-login.
Cache hits: 13: 0.00159s for 26,731 bytes (show)
Cache misses: 2: (show)
Queries used: 15.

[Show Queries]