Linking Org Chart Images

A newsgroup poster asked a question this week about how he could link images within his Org Chart data.

Under normal conditions the Org Chart addon enables you to add images on a one-by-one basis.  The Insert Picture... context menu option produces a file picker dialog that waits for the user to select an image file before continuing its process and this gets in the way of any proper programatic access...

My proposed solution is to use a little code to simulate the addon's Insert Picture function, but I'm intentionally not going to go into too much detail about how this works in this post so that I don't scare off the non-coders amongst you.  If you are in this group then don't worry - if you know how to Copy and Paste then you should be able to work through following example fairly easily.  If you're in the coding group though, I used the SDK's Event Monitor tool to understand what the Org Chart is up to and I'll elaborate on this in the next post.

The basic process is as follows:

• add a column of image file names to your Org Chart spreadsheet data
• create your Org Chart
• run some code to review all your shapes and where it finds an image file path, add the actual image to the respective shape

Using the linked image code - A walkthrough

1. in Excel, start by adding a column to your existing Org Chart spreadsheet named 'ImageFile'
2. add an associated image file name to each row
3. in Visio select File / New / Business / Organization Chart Wizard
4. run through the wizard and when you get to the Shape Data step, ensure you add the ImageFile item to the right hand column
5. as you complete the wizard, a new Org Chart should be created
6. save the file to a specific folder and ensure that your image files (that you specified in your spreadsheet) are also in the same folder
7. now for the Copy and Pasting part - Download the zip file at the bottom of this post, open the OrgImageCode.txt file within it and copy the text (press Ctrl+A to select all of the text and the Ctrl+C to copy it)
8. back in Visio, press Alt+F11 to launch the VBA editor (VBE)
9. on the left hand side, in the Project Explorer, double click the ThisDocument item, which should open a blank code pane on the right
10. click in the right hand code pane and press Ctrl+V to paste your code and then close the VBE.
11. with your Org Chart showing in the drawing window select Tools / Macro / ThisDocument / RefreshOrgChartImages
12. the images should be added to each shape that contains a file reference.  If this doesn't work check that your file matches the sample in the zip file below or drop me a line in the comments below.

If you want to use full file paths in your data (and so open the possibility of diffent file locations) then you just need to edit a couple of lines in the code - Have a look at the Get the file name for the associated image section in the AddImage function.

Anyway, I hope I've been clear enough for the non-coders out there and if not then please do add a comment below.

You can download a zipped file set of an example Visio doc, the Excel data and associated images here: Download LinkingOrgChartImagesSample.zip

Comments

[Nasser]

Hello John

I'm facing a challenging issue , yet you have give me some clues, I have an Excel sheet that would use to create an org-chat using Visio 2007.
, I have a column with nationally information, the challenge is I need to
have a small flag represent each employee nationally inside his box.
same concept cab used but still I'm not there yet

Appreciate your guidance achieving my goal

[John]

Hello Nasser,

I think David Parker may have answered your question over here: http://bvisual.spaces.live.com/blog/cns!3350D61BC93733A9!521.entry

Best regards

John

[john constable]

for some reason I can't download the zip. Is it still available, please?

[John Goldsmith]

Hello John,

Sorry to hear you're having download problems.

I've just had a quick go and it appears to work ok for me I'm afraid.

Let me know if the problem persists and I'll email you a copy.

Best regards

John

[john]

tried again tonight and it worked perfectly this time, Jon. I can't believe I've 'written' my first VBA macro too! Thanks so much for this. What a shame visio doesn't do this by default.
A couple of quick questions if I may:
1) does this work with 2007?
2) if I change the orgchart data and import a new updated chart through the wizard, do I have to repeat the whole macro creation process again?

[Susan]

Would it be possible for you to e-mail the actual VBA code to me? I've been assigned the project of creating a picture org chart, and your solution would help me immensely! Our company firewall does not allow for downloads. Your help would be greatly appreciated!

[John Goldsmith]

Hello Susan,

Have emailed you just now.

Best regards

John

[Eddy]

Hello John,

I am facing an issue when I generate organization chart from an existing data file using visio 2003 organization chart wizard.
When I regenerate the chart using the same data file, the department sequence changes. For example, for the first time, it displays Department A, Department B and then Department C, but next time when I regenerate it using the same data file and same settings, it displays Department B, Department A and then Department C.

Do you know how I could keep the sequence unchanged when I use the wizard? if yes, could you please post it here or email to me at xxxxxxxx?

Thanks a lot!

Best Regards,
Eddy

[John Goldsmith]

Hello Eddy,

I'm afraid I'm not sure off the top of my head but I'll have a think and get back to you (I did get your email address by the way).

In the meantime, have you asked on the Visio newsgroups?

Best regards

John

[Kevin Warner]

This is beautiful. Thank you.
I have a small problem.
When I am trying to update the photos, I get the following error on the folling vba line:
ActiveWindow.Select shpParent, visDeselectAll + visSelect

err.number = -2032465762
err.Description = Inappropriate target object for this action.

This is from the section that is trying to delete an existing picture.
I have been unable to find any substantial documentation for Visio and so I'm unable to work this out. Do you have any idea what's going on here? I'm using Visio 2007 standard edition.

Thanks for any help,
Kevin Warner

[Kevin Warner]

I fixed it by adding the following lines:
Application.ActiveWindow.Page = pag
ActiveWindow.DeselectAll
ActiveWindow.Select shpParent, visSelect

Thanks again for this code.
Kevin Warner

[Coco]

Thank you so very much for this. I have been searching for a solution for days. I an a non-coder (although trying to learn) and I'm having a problem with the VB code when using your example. When I run the macro, the VB code comes up with this line highlighted in yellow:
Public Sub RefreshOrgChartImages()
and in the line below, the word Pages is highlighted
For Each pag In ThisDocument.Pages
plus an error box comes up that says Compile error: Method or data member not found.

[John Goldsmith]

Hello Kevin,

Thanks for your posts and taking the time to feedback the answer.

You're quite right, the problem is that the ActiveWindow needs to be the page being operated upon and I'm missing that part. This could be an issue for a multi page document or if the code is called when the drawing window is not the ActiveWindow.

I'll correct the code and update the download document.

In the meantime you just need to insert the line:

ActiveWindow.Page = pag

...above this line (which can remain the same):

ActiveWindow.Select shpParent, visDeselectAll + visSelect

Thanks again Kevin for spotting the mistake.

All the best

John

[John Goldsmith]

Hello Coco,

Sorry to hear you're having problems. Do you want to have a look at my reply to Kevin and see if that helps.

Let me know if that helps or if you need more information.

All the best

John

[Coco]

That worked! Thanks again. Just one more question. Can you point me to the place in the code that makes George Bush's image larger than the rest? Thanks

[Simon Goodier]

I've made a similar one; where the shapes are exported from LDAP straight to CSV; with a PHP based URL that extracts the thumbnailPhoto and sticks on a PNG file header as part of the HTTP request...
so visiting the URL: http://somewebserver.blah.com/thumbnailPhoto.php?user=sgoodier
will render a PNG image.

This means, if I escape the HTML hack that microsoft introduced; then it renders just fine :)

anyone wanna see it, just ask.

[TJ]

John,
This macro and instructions was great. I myself had some problems getting it up and running thus had to take your example and work backwards. I have the macro input pictures. I wonder now though when I have the chart updated and organized as I want can I keep it synched up wit the excel file? Or do I have to use Access with this? I also see when I run the macro a second time (say to update new images) it causes a bug in the program. It resizes the previous images and then stops. Be nice if you can mod the macro to update the images with out adjusting the format or crashing. Thanks for this work you posted ... great stuff.

[John Goldsmith]

Hello TJ,

Regarding your initial problem, where did you find things were not working (which line and what error)? The code should deal with replacing existing images so I'd be interested to know where this is falling over for you. Also, which version of Visio are you using?

How to keep your Visio document synchronised with your data is one of those 'it depends' questions. How large is the data you're dealing with? How frequently do you want to update? etc. Any data source will work, from Excel to Access through to SQL Server. You just need code to get one to talk the other and decide on a strategy for resolving conflicts - ie do you want to draw from your data source and then edit and push back changes from Visio or just have a single direction data flow.

Finally, just to be sure, did you see the follow up post to this one (http://visualsignals.typepad.co.uk/vislog/2008/06/using-the-visio-event-monitor-tool.html)?

Best regards

John

[Dave]

Hi There- First of all, thank you so much for this code. It works like a charm. I am having an issue with some of the images cutting off the shape border, however. is there a way to add a margin or some padding between the image and the shape its being inserted into?

thanks much! -dave

[Suneel Singhal]

can same can be done when connecting with ODBC for oracle.

[Andreas]

Hi all,
thanks for the greate code. it is really helpful.
Anyway I have troubles with the code. I create an orgchart on 3 pages (always with the head of the departments) and when I try to link the images, I always get the error message "inappropriate target for this action" on line ActiveWindow.Select shpParent, visDeselectAll + visSelect.
I tried to add as mentioned above the additional code but then I get the same error on this line: ActiveWindow.Page = pag
does anyone know this issue and can help me?

[Norm Lacroix]

Hello John,

A fine gentleman, Scott Helmers shared your blog with me on LinkedIn because I was trying to automate inserting pictres into my Org Chart. Initially I was getting an error "Unexpected End of File" Scott pointed out the a line from you script was in error sImageFileName = shpParent.CellsU("Prop." & CONST_IMGCELL).ResultStr("")
he recommended removing the "Prop." line to sImageFileName = shpParent.CellsU(CONST_IMGCELL).ResultStr("") this worked in that it stopped the error but I am not getting the pictures so there is still something wrong. I am using Visio 2010. any ideas??

Thanks,

Norm