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

Visio backward synchronization

Started by Nikolay, December 02, 2014, 05:38:44 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Nikolay

Hello all,

Recently I've tried to do what was written here:
http://visguy.com/vgforum/index.php?topic=5750.msg23857#msg23857

Namely, use Visio data-binding infrastructure, but allow to update the data in the external source from Visio (back-synchronize).
Surprisingly enough, it looks not that bad; with a small twist, you can turn Visio DataRecordset into one which can be updated.
Furthermore, simultaneous editing of the same data in Excel and Visio seems to be no problem (i.e. you can keep Excel file opened, and refresh data in that file from Visio)



Here is a video (please turn on the subtitles - the comments are in the subtitles):
http://youtu.be/5sjfBNzVTrQ

Here is the installation of the Addin which adds the "Synchronize all" button (VSTO-based, should work with Visio 2007-2013):
http://unmanagedvisio.com/upload/back_sync/setup.exe

Here is the source code:
https://github.com/nbelyh/VisioBackSync

How it works:
- On synchronization, it queries the data directly from the external data source using good old ADODB (using connection data from Visio, such as ConnectionString and CommandString)
- Then, it analyzes the changes in the data source and on the diagram; if there are conflicting changes, it shows the resolution window.
- If no conflicts, then it updates the data source directly using ADODB, and then Visio data using DataRecordset.RefreshFromXml

Now works only if there is a primary key (i.e. does not work when "row number" is used as a key)
Now it works for updates only; row/shape inserts/deletions are not processed. In principle, I think this could be added as well (via conflict resolution window)
Not all types of data sources are supported; have tested for Excel, MSSQL, Access.
Not all data types are supported; have tested only with string/number/boolean/datetime.
Will not work with SharePoint list for sure.

BTW, the project uses this template :)

Any feedback is highly appreciated!

Also, since it's very first version, I would recommend to make a backup before trying it.
Provided under MIT license.

Paul Herber

MIT
Magnificent, Incredible, Terrific.
Electronic and Electrical engineering, business and software stencils for Visio -

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

Nikolay

I've learned there more interesting one: WTFPL  ;D

Yacine

#3
My highest appreciation.
Well, maybe not the very highest, but very very high (VERY!). I did implement the same thing myself, but you're the very first to publish it.

And thumbs up for WTDPL!


By the way,why did you publish it under Visio Marketplace & Classified Ads?
Yacine

Nikolay

#4
Quote from: Yacine on December 02, 2014, 08:00:21 PM
By the way,why did you publish it under Visio Marketplace & Classified Ads?

No idea. I thought this group was created to put addins.. Where to put it then?

wapperdude

Seems like the correct place or User submitted stuff.

Wapperdude
Visio 2019 Pro

Paul Herber

Maybe "User-submitted stuff" might be more appropriate. ....
It is done.
Electronic and Electrical engineering, business and software stencils for Visio -

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

it_consultant

Thanks a lot for that solution, you guys saved my life ! ! ! THAAAAAAAANK YOU!!!!!!!!!!

Does anyone know how to programmatically simulate a click on that button in the ribbon by using vba?

A pragmatic approach of mine was to use the "sendkeys"-function, because it's easy and sufficient. The problem with this approach is, i cannot navigate to the main-button (marked as 3. in the picture) by using the shortcuts "ALT+V" and "B" via sendkeys, i'm only able to navigate to the "conflicts panel" button as a subitem of "Synchronize All" (marked as 2. in the picture).

Does somebody have an idea how to click on the main-button (marked as 3. in the picture) via sendkeys or in a different way?



I'm just use this:

SendKeys "%v", True
SendKeys "b", True
SendKeys "{ENTER}", True

Google gave me not much to accomplish this task, please help me out.


Best regards

Joerg

Nikolay

Hi!
I am not sure if it is possible to click ribbon button programmatically, but it could be possible to just add some sort of "API" to call from VBA..
So that you can call it programmatically, like this:

Application.ComAddins("BackSync").Object.DoTheSync()

it_consultant

#9
Hi Nikolay,

thank you for your reply. How can i add this api-function? Because: Application.ComAddins("BackSync").Object.DoTheSync() won't work in the vb-editor window :(

MsgBox Application.COMAddIns.Item("BackSync") <- Print "BackSync"

So which method do i have to call?

Nikolay

It does not exist yet :)
The programmatic usage was not foreseen actually.

I.e. need to add it to the addin first then recompile/reinstall. I think I can add that today evening.

it_consultant

Ah, okey. Unfortunately i don't have a visual studio here to recompile it.

It would be awesome if you can do that   :D

It's maybe helpful for others too.

Thank you very very much for your support!

Nikolay

Published the updated version.

http://unmanagedvisio.com/products/backsync-backward-visio-data-synchronization/

What's new:
- Preview changes
- SharePoint backward sync now works
- Form like the built-in "refresh data"
- Add/Remove rows (see the ink above for details)

You should now be able to call it like this:

Application.ComAddins("BackSync").Object.Synchronize

Hopefully have not broken anything :D

it_consultant

#13
Hi Nikolay,

thanks a lot for your work!

If i fire the command

Application.COMAddIns("BackSync").Object.Synchronize

in the Document_DocumentOpened method i got the error "The given key was not in the dictionary - runtime error".

I've fixed that by using the Document_RunModeEntered method instead, and then it works to fire the event at the time the document was opened. So that's fine  :)

But i also want to execute the sync when i close the document by using the command within the Document_BeforeDocumentClose method. If i close the document by clicking on the x in the upper right corner or navigate to file > close i get an error "The object reference wasn't set to an instance of an object - runtime error".

What i'm doing wrong?

Thanks for your brilliant work so far!!!


Best regards

Joerg

Nikolay

Will check this today. I just tested by running it from the Intermediate window :D

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: 449 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1289KB.
Tokens: post-login.
Cache hits: 14: 0.00218s for 26,721 bytes (show)
Cache misses: 4: (show)
Queries used: 17.

[Show Queries]