Visio Guy

Visio => User-submitted Stuff => Topic started by: Nikolay on December 02, 2014, 12:38:44 PM

Title: Visio backward synchronization
Post by: Nikolay on December 02, 2014, 12:38:44 PM
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)

(http://i60.tinypic.com/24wy6mu.png)

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 (http://visguy.com/vgforum/index.php?topic=6080.msg24705) :)

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.
Title: Re: Visio backward synchronization
Post by: Paul Herber on December 02, 2014, 01:33:12 PM
MIT
Magnificent, Incredible, Terrific.
Title: Re: Visio backward synchronization
Post by: Nikolay on December 02, 2014, 01:59:29 PM
I've learned there more interesting one: WTFPL (http://en.wikipedia.org/wiki/WTFPL)  ;D
Title: Re: Visio backward synchronization
Post by: Yacine on December 02, 2014, 03:00:21 PM
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 (http://visguy.com/vgforum/index.php?board=13.0)?
Title: Re: Visio backward synchronization
Post by: Nikolay on December 02, 2014, 03:09:24 PM
By the way,why did you publish it under Visio Marketplace & Classified Ads (http://visguy.com/vgforum/index.php?board=13.0)?

No idea. I thought this group was created to put addins.. Where to put it then?
Title: Re: Visio backward synchronization
Post by: wapperdude on December 02, 2014, 03:50:09 PM
Seems like the correct place or User submitted stuff.

Wapperdude
Title: Re: Visio backward synchronization
Post by: Paul Herber on December 02, 2014, 05:57:21 PM
Maybe "User-submitted stuff" might be more appropriate. ....
It is done.
Title: Re: Visio backward synchronization
Post by: it_consultant on February 23, 2015, 05:41:22 AM
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?

(http://img4.picload.org/image/cpcgidr/ribbon.png)

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
Title: Re: Visio backward synchronization
Post by: Nikolay on February 23, 2015, 05:46:24 AM
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()
Title: Re: Visio backward synchronization
Post by: it_consultant on February 23, 2015, 07:35:35 AM
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?
Title: Re: Visio backward synchronization
Post by: Nikolay on February 23, 2015, 07:58:53 AM
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.
Title: Re: Visio backward synchronization
Post by: it_consultant on February 23, 2015, 08:17:39 AM
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!
Title: Re: Visio backward synchronization
Post by: Nikolay on February 23, 2015, 09:00:05 PM
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
Title: Re: Visio backward synchronization
Post by: it_consultant on February 24, 2015, 05:41:32 AM
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
Title: Re: Visio backward synchronization
Post by: Nikolay on February 24, 2015, 06:18:19 AM
Will check this today. I just tested by running it from the Intermediate window :D
Title: Re: Visio backward synchronization
Post by: Nikolay on February 24, 2015, 03:38:49 PM
Fixed (1.0.0.9)
Now there should be, first, a reasonable error message if you call the Add-in when there is no active document.
Second, you can now pass in the document to the Synchronize method (if not passed then active document is assumed by default)

I.e. now you can call it like this (where "doc" is the document you want to sync):

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

The issue was that when you either open or close document, active document is Nothing.
I.e. now it should work also from DocumentOpened / BeforeDocumentClosed (if you pass in the document)
Title: Re: Visio backward synchronization
Post by: it_consultant on February 27, 2015, 03:43:20 AM
Hi Nikolay,

i've now implemented everything, it isn't really fast over a networkshare but that's ok.

Thank you very much for your excellent work, you saved my life !


Kind regards

Joerg