Author Topic: Visio backward synchronization  (Read 11005 times)

0 Members and 1 Guest are viewing this topic.

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Visio backward synchronization
« 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)



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.
« Last Edit: December 02, 2014, 05:19:28 PM by Nikolay »

Paul Herber

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2656
    • Paul Herber's website
Re: Visio backward synchronization
« Reply #1 on: December 02, 2014, 01:33:12 PM »
MIT
Magnificent, Incredible, Terrific.
Electronic and Electrical engineering, business and software stencils and applications for Visio -

http://www.paulherber.co.uk/

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #2 on: December 02, 2014, 01:59:29 PM »
I've learned there more interesting one: WTFPL  ;D

Yacine

  • Hero Member
  • *****
  • Posts: 2499
Re: Visio backward synchronization
« Reply #3 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?
« Last Edit: December 02, 2014, 03:04:02 PM by Yacine »
Yacine

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #4 on: December 02, 2014, 03:09:24 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?
« Last Edit: December 02, 2014, 03:13:22 PM by Nikolay »

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3296
  • Ideas Visio-lized into solutions
Re: Visio backward synchronization
« Reply #5 on: December 02, 2014, 03:50:09 PM »
Seems like the correct place or User submitted stuff.

Wapperdude
Visio 2007 Std

Paul Herber

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2656
    • Paul Herber's website
Re: Visio backward synchronization
« Reply #6 on: December 02, 2014, 05:57:21 PM »
Maybe "User-submitted stuff" might be more appropriate. ....
It is done.
Electronic and Electrical engineering, business and software stencils and applications for Visio -

http://www.paulherber.co.uk/

it_consultant

  • Jr. Member
  • **
  • Posts: 11
Re: Visio backward synchronization
« Reply #7 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?



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

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #8 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()

it_consultant

  • Jr. Member
  • **
  • Posts: 11
Re: Visio backward synchronization
« Reply #9 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?
« Last Edit: February 23, 2015, 07:41:57 AM by it_consultant »

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #10 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.

it_consultant

  • Jr. Member
  • **
  • Posts: 11
Re: Visio backward synchronization
« Reply #11 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!

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #12 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

it_consultant

  • Jr. Member
  • **
  • Posts: 11
Re: Visio backward synchronization
« Reply #13 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
« Last Edit: February 24, 2015, 05:43:47 AM by it_consultant »

Nikolay

  • Hero Member
  • *****
  • Posts: 764
    • UnmanagedVisio
Re: Visio backward synchronization
« Reply #14 on: February 24, 2015, 06:18:19 AM »
Will check this today. I just tested by running it from the Intermediate window :D