Export shape-texts to Excel - multilanguage support

Started by visio(nary), June 13, 2014, 06:38:56 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

visio(nary)

A good morning to all of you :D

I have an issue regarding multilanguage support in Visio-drawings.
The Question is that if it is possible to export all shapes (shape-texts) within
a drawing in either German or English.

In Excel or Access that would be easy for me. I could use a worksheet or table
and store all words and their translations. With a generic function I would then
be able to request a 'keyword' in a specific language and get the appropriate
language back.

But how to accomplish this in Visio?
There is no table to store words/values, ....
Should I use a class in an external stencil-file (one property per keyword)
Can I implement a xml-file holding all words?

Once aganin, many thanks for your support!
visio(nary) - and yes Yanice my name is Mike  8)

Paul Herber

Shapes have a shapesheet behind them to hold data, there are various sections including Shape Data and User-defined Cells. You could create cells to hold the appropriate text.
Electronic and Electrical engineering, business and software stencils for Visio -

https://www.paulherber.co.uk/

visio(nary)

Hello Paul,

thanks for your reply!

The idea is good - problem is, that the texts are then directly hardcoded in each drawing and that is why I prefer
an external ressource file that can easily be updated.
I now created an Excel file with all ressources and a key-column. I open the workbook directly in Visio and read all
texts into a dictionary object. This object has one unique key-column and an array (languages) for each row.
The problem is that I would need a unique identifier (key) for every text entry in any shape object and that is a quite
impossible purpose  and hard to handle :-\ Hhhmmmmmm

Thans again for your answer!
Looking forward for any other ideas...

vision(nary)

aledlund

"In Excel or Access that would be easy for me. I could use a worksheet or table
and store all words and their translations." If you're going to the trouble of creating tables for the data, you can import the tables as datarecordsets. Another method would be to save the text translate as xml and take advantage of 'solutionxml' to save it in the document.
al

Jumpy

I don't understand the problem because I can't see what you have in Visio.

You have a shape. What information is in that shape and where?

You have only the text? Or do you have some shape data with information (e.g. the key to your language table, or at least the language the shape currently is in?

JohnGoldsmith

John Goldsmith - Visio MVP
http://visualsignals.typepad.co.uk/

visio(nary)

@Jumpy

Yes, I have a shape with shape data like 'Typ', 'Hersteller', 'Leistung', 'Eigenschaft', ...
I would need a method for having not only multilanguage captions (in that case 'Type', 'Manufacturer', 'Power', 'Feature')
but also multilanguage values of that properties like 'Temperatursensor' -> 'Temperature probe'.

Quotee.g. the key to your language table, or at least the language the shape currently is in?
Where would you store your 'languate table'? Do you mean an external ressource file or Vision internal properties?

Many thanks in advance!
visio(nary)

Jumpy

The "displayed name" of a property can be found in the ShapeSheet in the column prompt(?) and could be changed according to a translation table.

The values of a property are often choosed from a fixed pick list that is stored in the column Format(?). In such cases you will find in the value column a formula with INDEX or was it LOOKUP, that points to that list.
So what you have to do is to create translations for each fixed list, too, and place them in the translation table, and switch the list in the format cell according to the selected language.

visio(nary)

@Jumpy

Thanks again for your reply!
I've checked the ShapeSheet and found out that my values are all stored in the field "Value".
The fields "Prompt" as well as "Format" says "No Formula". Nothing changes if I edit these fields...

According to the language table I have to ask once again.
Where would you store all text ressources? In an external file?

Thanks!
visio(nary) - Mike

Yacine

#9
Hi Mike,
guess we are not yet enough people contributing to this post  ;D .
The thing that the other guys tried to explain, is that all depends on the visio solution, you're trying to build.

There's the "classical" structure. With a database and a visio drawing. The shapes would be bound to records in the DB and get the appropriate values.
In the DB, you would set up a "language_Export" table in which you would switch the output language. A data update in Visio, would then bring the new data to the shapes.

You may of course have another structure, in which Visio is the master. Then you would probably make the switch in Visio itself. --> either by macro or shapesheet formula.

I would however warn to use shapesheet solutions, as they are very difficult to maintain. You'll have to do a lot of nasty code manipulations in drawings and stencils to get new values in them.

Next question would be about the further use of the language related data. Are they displayed on the shapes, do you build reports, ...?

I enclose an example of the classical configuration, to show how easy the switch could be.
I hope the links to the DB are relative, otherwise you'd need to relink the data to "Data_Export"
Whenever you want to switch the language, run "Create_Data_Export", chose the language code and you get the right link table.
In the visio file, you'll need to refresh the data.

HTH,
Yacine
Yacine

visio(nary)

Hi Yacina,

many thanks for your explanations and your sample data which helped me a lot!
I did a quick test an created a test database in which I added some test data and it worked like a charme.

Problem is that I have many many shapes in my drawings and I have to connect each single instrument to the database -> lots of work!
But okay, that is not a matter of a bad concept but rather a work that has to be done sometime ;)

Is there a way to switch the language directly in Visio within the shape data?
I didn't come to a solution yet because I need a primary key for the shape. Since I have different languages (texts) for one part
in the database the part-id is certainly not unique any more - sure...
Despite I like your approach with the query (macro) in Access which generates the output table I don't think that I want to
give our "frontend" users direct access to the database.

Many thanks again for any suggestions!
visio(nary) - Mike

Yacine

Hi Mike,
it's now up to you to do your homework.
But here are nevertheless some hints:
1) there's an autoconnect feature, which requires a unique ID of the shapes. Guess you have the ID already.
2) you can run a MS Access query from within Visio. There are many examples in the net.
3) in the DB the parts have a unique ID, not the translations. In the export-table however the query writes the ID of the part. Otherwise the shapes would lose their connection to the records.
4) There are very easy ways to limit the user rights in an Access-DB. As my example shows, 3-4 dialogs are enough to work with the DB. You'll also need to give the user the possibility to edit the translations anyway.
Yacine