Seeking Help With Broken Legacy Tables

[cgpryor]

My company has a library of visio documents containing specification
information in hundreds of tables that were originally built in Visio 2002.
We want to edit and build on the data, but the tables don’t act the way one
might expect.

Adding rows or columns doesn’t work. Selecting rows or columns or multiple
cells doesn’t work. My team is unable to come up with a way to bulk copy and
paste or export or convert the table data into any other kind of usable
format (an excel spreadsheet, for instance). We can copy and paste the whole
table object, and we can edit individual cells, but that’s about it.

This is a significant roadblock for our project, and it would be an equally
significant service if anyone was able to offer a solution.

Download an example table: http://alphamark.com/example_table.vsd

 

[John]

Hi there,

I guess there are a number of options, but the suitability will depend on
how many files you have to deal with and how frequently this needs to be
updated.

As a first shot, one method of retrieving the data in the tables would be to
run a Report (Data / Reports... in 2007) and export to Excel. I've just
tried this by selecting the Displayed Text and the X Y coordinates. You can
then sort on the X Y coordinates in Excel to get the data into the right
order. (Note you need to ungroup the table to run the report by setting the
group shapes LockGroup cell to 0 in the respective shapesheet's Protection
section.)

Other alternatives, particularly if you have a large number of files, would
be to run through them programatically. Again, my suggestion would be to
use the shapes' PinX/Y cell values to ensure the retrieved data is sorted in
the correct order.

Once you've got hold of the data in a central location, I would create a
table row shape and then look at Data/Link Data to Shapes... (in 2007) and
or the Database Wizard addon in 2007 and earlier. Note that the Link Shapes
to Data functionality is a one way operation (ie you can update data from
the source to the Visio document, where as Database Wizard allows you to
push and pull data in both directions.

Anyway, I hope that helps.

Best regards

John

 

[cgpryor]

Thanks John for your help!

I understand that in order to execute your solution, I must ungroup the
table. I'm working in Visio 2003, and have disabled all protection on the
table, but I'm still unable to ungroup the table object. I get an error
message: "shape protection and/or layer properties prevents the full
execution of this command."

I can't recall ever working with a visio object that I couldn't ungroup, but
this one is inexplicably tricky. Are you able to ungroup the table I posted
in Visio 2007?

Thanks again for your help!

 

[Paul Herber]

Thanks John for your help!

I understand that in order to execute your solution, I must ungroup the
table. I'm working in Visio 2003, and have disabled all protection on the
table, but I'm still unable to ungroup the table object. I get an error
message: "shape protection and/or layer properties prevents the full
execution of this command."

I can't recall ever working with a visio object that I couldn't ungroup, but
this one is inexplicably tricky. Are you able to ungroup the table I posted
in Visio 2007?

press Control A to select the whole table
menu Windows -> Show Shapesheet
scroll down to the Protection section
and set Lock Group to 0
Close the shapesheet window.
Now you will be able to ungroup the table.

 

[srileo]

John,
I am Chris' colleague, working on the same document. Your method of
exporting all the tables to Excel is appealing, but the problem for us is not
so much gathering our broken visio data into Excel. It is making the round
trip back from Excel to Visio that is the problem. We might need to do
extensive Search and Replace actions and as far as we know, embedding or
linking an Excel sheet in Visio will not allow for Search and Replace
functionality.

Do you know otherwise?

 

[John]

Hello there,

Well my suggestion of exporting to Excel is really so that you can get the
data in one place. Once there you could then recreate new tables and setup
some kind of sychronisation that would more robustly link the shapes and the
data. I would certainly encourage you to look at Visio 2007's Link Shapes
to Data functionality as it is very easy to use, but also as I mentioned the
Database Wizard addon as both these method create a reliable link to your
data.

In terms of Search and Replace, it sounds like (although I'm making a number
of assumptions) this would best take place in Excel which would then refresh
the data in the Visio shapes.

I'm not sure from your posts whether you would be comfortable with creating
any automated systems (using VBA, c# etc) but if you are then this would
offer up a much wider list of options to you and if you're not then there
are many consulting businesses out there that would do the same.

In any case it sounds very much like you would benefit from a new book by
David Parker (www.visualizinginformation.com) in which he dicusses pretty
much all of the issues that your facing.

Anyway, I hope that helps, but if you'd like more advice it might be useful
to know a few more details about your particular scenario ie what version/s
of Visio, how many users, how often does data the change is there anything
else in the Visio docs aside from the tables and what do the end users of
the information use it for etc.

Best regards

John

PS - I'm just re-reading your question and I'm not suggesting you "embed" an
Excel worksheet within a Visio document. Search and Replace could be done
programatically but I'm guessing that Excel is the better place to edit your
data and then update Visio.

 

[srileo]

John,
thanks for the detailed reply. Both Chris and I are using Visio 2003. And
sadly, we are both doubtful of upgrading to Visio 2007 because of company
policy. We also need to share these documents quite frequently with other
colleagues offsite.

I'll answer your other questions in a couple of hours..