Custom Export To Excel

[RDSE]

Hi all,

I hope this is the right place to post this – apologies if it isn't. I thought I was trying to do something straightforward, but apparently not. It seems what I want is unusual so maybe, before asking "how", I ought to fill in "why".

I am building a new graph type database and, despite being "schema-less", as with most things, it pays to think about exactly what you are doing before you go blundering in! I am finding that the best approach is to break the thing into bite sized chunks that can be displayed as a 2-D diagram – hence Visio. Only one shape and one connector type are required; the shapes represent "nodes" and contain one or more text "Labels" plus between 0 and "n" "Properties" in the form of key:value pairs. The connectors are uni-directional arrows representing "Relationships" and feature text to define their "Type", they too can have 0 to "m" key:value pair "Properties". The relationships can be between any two nodes and there is no limit to the number of relationships any node can have in either direction. Being an engineer my instant approach would be to use Excel because "You can do anything with that, can't you?" However, given relationships going all over the place the diagrams become quite a complex even for small chunks of the whole – so you really need the auto-layout/spacing etc tools that Visio offers!

So far, so good and I have my first "Bite-sized diagram" – I think the trick is defining the size of the bite in order to keep the diagram comprehensible! So, I got to the point of wanting to export that structure into Excel in a useful format only to find: I can't! Please correct me if I am simply missing something but what I need is an Excel file showing:

Sheet 1

1)   Column A - nodes: A list of Node IDs – row spaced to accommodate the labels and property values.
2)   Column B - labels: the text corresponding to the labels – as many rows as it takes
3)   Column C and D - properties, key/value: the text corresponding to the key and value pairs - one row per pair as for labels

Sheet 2

1)   Column A – relationship ID: A list of Relationship IDs – row spaced to accommodate the property data. The rows in which these values appear are shown as row "x" below
2)   Cell B"x" – relationship type: the single value corresponding to "type"
3)   Cell C"x" – Start node ID: the Node ID for the "from" node – i.e. where the relationship originates
4)   Cell D"x" – Finish node ID; the Node ID the relationship points towards
5)   Columns E and F (using rows x+1, x+2 etc one row per pair as required): the key/value pairs for the relationship properties

If I had this, it would be easy to get Excel to reformat it into a form the database can easily inhale. Given that this is nothing more than a listing of all the data entered, shorn of any style or geometry information I would have thought it easy to extract. But I can't find how to do it! The Report system just seems to list an "inventory" of text entered without any record of the relationship between the shapes and connectors. Or am I missing something?

This is really holding me up and I shall have to find some other approach if I can't get Visio to do this! I am quite prepared to buy an add-in if required – but I can't find one. Any help would be much appreciated. I suspect other "Graphistas" would like it too!

[Yacine]

- - - NICE - - - Thanks for the challenge.

First thoughts:
1)
Speaking about databases, why Excel? Access is such a nice tool!
From what you describe, I understand you're trying to press the DB in a relation based DB. That's what Access "excels" in.
2)
I understand the aim of the game is the DB. Visio is just the input tool?
Fine.
3)
Visio has unfortunately been "re"-designed to be either a simple drawing tool, or a "sink" for data provided by other applications to transform into neat graphics.
The way back from Visio to the data sources has been neglected. (Very bad decision IMHO)
Luckily this way is not completely blocked, as there are numerous ways to get the data from Visio to other applications (Excel, Access, etc.)

4)
The data, you're missing lay in the connections objects of your Visio file.
There are plenty of information in this forum about how to extract them. The main contributors were probably Al Edlund and lately Wapperdude.

This said, it's 10 past midnight, goodnight!
Let me know your thoughts,
Y.

[Croc]

This is really holding me up and I shall have to find some other approach if I can't get Visio to do this! I am quite prepared to buy an add-in if required – but I can't find one.

RDSE,
This problem is solved by using a macro. The programmer will make such macro for 1-2 hours. Ready application does not exist.

[RDSE]

Hi Yacine and thanks for your response - which helps fill in the blanks for me. Yes, I am trying to use Visio as a means of laying out a structure, one piece at a time in 2D, once all fully entered into the database this becomes a complex, 3D structure.  So, yes, Visio is a sort of input tool for me.

Why not Access? because Access is a traditional Relational Database and is, I am afraid utterly useless for the database I have in mind, my database is being constructed in neo4j which is a Graph Database

I agree with your conclusion that re-designing Visio to be a means of swallowing data without sensible regurgitation facilities seems rather foolish!

I will check the contributions of the people you mention - I must admit I am rather hoping that someone, somewhare will have something I can use "off the shelf"...

Croc - many thanks to you too, I rather thought that what I need can be programmed into Visio using VB Macros - but I am not a VB programmer and neither do I have any desire to become involved in Visio more than I need to in order to use it as a tool for this specific purpose - believe me, I have enough on my plate learning Cypher (for neo4j), html, css and Javascript all at the same time!

If there are any programmers here who would like to offer writing such a macro as a paid for service (in which case a custom node block and connector text format would be nice too) I would be delighted! I hope it is not forbidden to make such requests here!

Conclusion so far - yes it is possible, no there are no "library packages" out there that do the job. Volunteers?

[Croc]

email me gCroc@yandex.ru
I'll try to help you.

[Croc]

Take a look at the first test version in the DropBox.
https://www.dropbox.com/sh/xfx8r97fra4to7n/AABOwXAfH2RdT5e90yhoRkZZa?dl=0
Download the entire folder, open the scan2.vsd file and run m1.ttt macro.
Animated GIF scanner2.gif demonstrates how this works.
The Excel file is created on ExlFile.xltx template.
If I have misunderstood something, write about it.

[RDSE]

Thanks Croc! I am on the road for work at the moment and won't get back to my desktop with Visio until Tuesday, I will try it then!

[RDSE]

Croc,

That is a great step forward! In fact, I think it may be even closer to what I seek than first appears. This is because I am not sure where your values that appear as "p1", "p1_value", and "conProp1", "conProp1Value" appear in the Visio diagram - if I did it may well be that the format of the excel is very close indeed.

In the attached files I have added more info to each of the shapes in your visio example (the actual info is meaningless, just there for example purposes). I have then run the macro to output this data. I have then added two sheets to your spreadsheet to show how i would like the data to go into the excel in order to make it easily usable for my purpose. I have worked out three options because, once it is in then I can manipulate within Excel and i don't know which macro is easiest in Visio.

As I say, I may simply be missing something in the base formatting of your Visio shapes and, if so, I apologise! The excel headings are just to ensure that we are not at cross purposes as to what is actually in each column!

Very many thanks for your help, i hope that this further question is not imposing upon your good will too much.

[Croc]

RDSE,
Try the new version

[RDSE]

Croc,

That is amazing! I have tested it with the sample sheet and have added some complexity - more nodes, more connections and it's fine. However, I was then bold enough to try it with my first real data diagram and I got an out of range error. This may simply be because there are a lot of shapes on the real data diagram (though later ones will be bigger) and a count maximum needs to be increased or it may be that the node shapes in my real data are not identical to the base diagram - in which case I can change them. As yet the relationships/connections in this real data have no associated key:value pairs but they will come!
My real data sheet is attached

Rgds, RD

[Croc]

RDSE,
Nodes in this file have a completely different structure.
They need a different macro.
Check the attached file.

[Croc]

RDSE,
I think your problem is very specialized. It makes no sense to solve it on the forum.
I propose to go to the e-mail.
E-mail me gCroc@yandex.ru