Microsoft Visio 2013 Shapes Data Linking using the embedded worksheet (OLE) object

Question

How can I link Visio 2013 Shape to the Data available within Excel Worksheet Object already embedded within the Visio document (OLE?) on one of the Visio pages.

I'm already aware of linking to Excel Data to an external Workbook/worksheet.  The advantage I'm thinking of doing this way is to keep both the Visio Diagram and data together handy in the same .vsdx file.

Appreciate any suggestion or alternatives.

Answer 1

The first step of course is to get the data into visio to start the process. You can accomplish this either manually using odbc to import the data or programattically. For a great tutorial on manipulating Excel I would check out this work by an Excel MVP

http://www.siddharthrout.com/vb-dot-net-and-excel/

Don't worry about it being in vb.net, it is close enough to vba to be not an issue.

The second step is to read the imported information from the datarecordset(s) as xml and then save them within the document. For this step I would get a copy of the v2010 SDK (which is free) and examine the example code.

https://www.microsoft.com/en-us/download/details.aspx?id=12365

There are several articles on-line which talk to solution xml that can give additional guidance.

hth,

al edlund

Answer 2

XML data attached and living in the same Visio doc is definitely next best option, although it takes coding to do that.

Can you describe the high level approach or example of using the XML data?

Thanks

Answer 3

The difficulty is that the datalinking functionality takes advantage of ODBC which does not have access to the embedded OLE object. For a recent project where excel data was desired, I ended up saving the data as XML in the document which then gave me a path to move it into the datarecordsets programmatically. Obviously significant custom coding required.

al edlund

Answer 4

Shashank, Thanks for looking into my question.

1. The primary motive was to ensure that the Excel Data is not lost and always available... if visio file is already there... The excel doc data on the side will be synced/consolidated from multiple sources.
2. The kind of diagram I had this need for is an Enterprise distributed Application with has the listing of all the architecture assets tied into the Excel.  Current scope for the Visio update will be single/exclusive user, although collaborated by multiple contributors with checkout out/in operations on the SharePoint.

It would be great for the Visio diagram to have access to the data that sits inside it's own house!  Maybe a future feature idea?

Thanks

Answer 5

Dear User,

This is an interesting scenario, however currently you cannot link your shapes to the embedded worksheet object, you can only link to external workbook as you mentioned.

Please help us understand the advantages (over linking to external source) further by letting us know the following:

• What kind of diagram and data are you linking together? If possible, please share the same with the mail id mentioned below
• Apart from keeping the diagram and data together, are there other advantages that you see by linking diagram to internal data?
• Do multiple users work together on the Visio file? If so, do they work collaboratively on the diagram as well as data?

Your valuable feedback will help us in further developing the product, Thanks!

Regards,

Shashank Gandhi

visio-feedback [at] Microsoft [dot] com