Author Topic: Refreshing Excel Linked Data Corrupts Data  (Read 3086 times)

0 Members and 1 Guest are viewing this topic.

axattenb

  • Newbie
  • *
  • Posts: 2
Refreshing Excel Linked Data Corrupts Data
« on: September 01, 2020, 02:20:04 AM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4050
  • Ideas Visio-lized into solutions
Re: Refreshing Excel Linked Data Corrupts Data
« Reply #1 on: September 01, 2020, 09:35:06 AM »
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

  • Newbie
  • *
  • Posts: 2
Re: Refreshing Excel Linked Data Corrupts Data
« Reply #2 on: September 02, 2020, 02:50:50 AM »
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

  • Hero Member
  • *****
  • Posts: 1072
    • UnmanagedVisio
Re: Refreshing Excel Linked Data Corrupts Data
« Reply #3 on: September 02, 2020, 07:31:09 AM »
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.

« Last Edit: September 02, 2020, 07:40:36 AM by Nikolay »