I was recently asked how to add a country flag to Visio Org Chart shapes automatically. Well, as I am currently working with world data at the moment, I have taken up the challenge. Firstly, the CIA have an excellent source of information, called the World Factbook ( https://www.cia.gov/library/publications/the-world-factbook/ ). You can find all sorts of information about every country in the world, including, of course, their flags. There are over 240 countries at the moment, and there are various codes used by different systems to identify them. Even the name may not be the same, for example, do I live in UK, United Kingdom, Great Britain, United Kingdom of Great Britain and Northern Ireland or just plain old England? Answers on a post card, please, addressed to …..?
Alternatively, a country can be identified accurately with a code, but you need to know which system is being used. United Kingdom can be identified by GB in the Iso 2 character system, or 826 in the Iso numeric system, or UK in the FIPS 10 system. The CIA World Factbook utilises the FIPS 10 code, so consequently, all of the country related web pages and images are coded with this system.
I have already created an Access database with all this information, so I decided to use this knowledge to create a Flags of the World Visio Master.
I wrote some VBA to download the gif files from CIA website, and imported them into a specially prepared group shape. Each image shape was automatically named with the FIPS 10 code, and the Width and Height formulae were written to be 0 if the parent group shape does not represent relevant country.
The ShapeSheet of the parent shape was loaded with semi-colon separated lists of country related data, mainly pulled from the CIA website. The Prop.Country Shape Data row is a fixed list so that the user can select a country by its name. The other Shape Data rows are all guarded because their values are pulled via the Index of selected country name, from the User defined cells.
ShapeSheet of the GB sub-shape, below, showing that the Width and Height is 0, if the shape Name is not equal to the Prop.Fips10 value in the parent shape. Notice that the Width and Height formulae maintain the original aspect ratio of the flag image.
This provides a Flags of the World Master, but I also created a Flags of the World Small Master because the first one uses the large flag images from the CIA website, but the second one uses the small image files, and thus uses less file size.
I have also added context sensitive hyperlinks to the CIA website, Maps Live, Google Maps and Google Earth, and one to bVisual’s website….
OK, now we are halfway there … we actually want to apply the map icon to any shape that has a Shape Data value that is a country name, but we don’t know the name of this Shape Data row in advance….
Visio 2007 Professional Data Graphics has Icon Sets, but you can only have a maximum of 5 icons in each set … certainly not enough for over 240 countries. So, why not use an Icon Set as a placeholder for a country flag, then replace the Icon Set image with the flag for the country whose value is in the Shape Data row specified by the Icon Set Graphic Item?
That is exactly what I did in the screenshot below:
I duplicated an existing Icon Set (Trend Arrow 1) in the Drawing Explorer, renamed it as FlagPlaceholder, then amended it to have one image sub-shape, called Flag.
Thus the Icon Set becomes available in the Data Graphics editor, and you can select the Shape Data row that contains the name of the country. You only need to have one qualifying value, so I entered does not contain * , because I assume that there is no country in the world with that character in its name.
The attached stencil, FlagsOfTheWorld.vss FlagsOfTheWorld.zip , includes the macro UpdateFlags, which will then change the flag for each selected shape.
So, when the macro is run, the test shapes now show the flags for each country….
Option Explicit
‘David Parker
‘July 2008
‘No warranties at all with this code
‘Use at you own risk
Public Sub UpdateFlags()
Dim shp As Visio.Shape
Dim subshp As Visio.Shape
Dim imgshp As Visio.Shape
Dim flagExists As Boolean
Dim cel As Visio.Cell
Dim celPrecedent As Visio.Cell
Dim aryPrecedents() As Visio.Cell
Dim country As String
Dim aryCountries() As String
Dim aryCodes() As String
Dim mstFlag As Visio.Master
Dim code As String
Dim idx As Integer
Dim filePath As String
Dim flagShp As Visio.Shape
Dim shpWidth As Double
Dim shpHeight As Double
Dim shpAspectRatio As Double
Dim flagWidth As Double
Dim flagHeight As Double
Dim flagAspectRatio As Double
Dim cloneShp As Visio.Shape
Dim cloneMst As Visio.Master
On Error Resume Next
Set mstFlag = ThisDocument.Masters(“Flags Of The World”)
If mstFlag Is Nothing Then
Exit Sub
Else
aryCountries = Split(mstFlag.Shapes(1).Cells(“Prop.Country.Format”).ResultStr(“”), “;”)
aryCodes = Split(mstFlag.Shapes(1).Cells(“User.Fips10”).ResultStr(“”), “;”)
End If
‘Need a temporary writeable file need
‘You may need to change this but it needs to be gif extension
filePath = Replace(Replace(ThisDocument.FullName, “.vss”, “.gif”), “.vsd”, “.gif”)
‘Get the current shape(s)
For Each shp In Visio.ActiveWindow.Selection
If Not shp.DataGraphic Is Nothing Then
For Each subshp In shp.Shapes
If subshp.CellExists(“User.msvCalloutType”, Visio.visExistsAnywhere) <> 0 Then
If subshp.Cells(“User.msvCalloutType”).ResultStr(“”) = “Icon Set” Then
‘We know that this shape is an Icon Set
If subshp.Name = “FlagPlaceholder” Then
‘We know that the FlagPceholder exists
‘Get the shape aspect ratio
shpWidth = subshp.Cells(“Width”).ResultIU
shpHeight = subshp.Cells(“Height”).ResultIU
shpAspectRatio = shpWidth / shpHeight
For Each imgshp In subshp.Shapes
If imgshp.Name = “Flag” Then
‘Found the Flag shape
Set cel = subshp.Cells(“User.msvCalloutIconNumber”)
If UBound(cel.Precedents) = 1 Then
‘Get the value in the precedent cell
aryPrecedents() = cel.Precedents
Set celPrecedent = aryPrecedents(1)
country = celPrecedent.ResultStr(“”)
idx = getLookup(country, aryCountries)
If idx > -1 Then
‘We found a match for the country name
code = aryCodes(idx)
If Len(Dir(filePath)) > 0 Then
‘Clear the temp file
Kill filePath
End If
Set cloneShp = mstFlag.Shapes(1).Duplicate
cloneShp.Cells(“Prop.Country”).FormulaU = “=””” & country & “”””
cloneShp.Shapes(code).Export filePath
cloneShp.Delete
If Len(Dir(filePath)) > 0 Then
‘Remove the previous flag
‘Temporarily unlock the group
subshp.Cells(“LockGroup”).FormulaU = 0
imgshp.Delete
Set flagShp = subshp.Import(filePath)
flagShp.NameU = “Flag”
flagShp.Name = “Flag”
‘Get the flag aspect ratio
flagWidth = flagShp.Cells(“Width”).ResultIU
flagHeight = flagShp.Cells(“Height”).ResultIU
flagAspectRatio = flagWidth / flagHeight
If shpAspectRatio > flagAspectRatio Then
‘Maximise height of flag
flagShp.Cells(“Height”).FormulaU = “=” & shp.NameID & “!Height”
flagShp.Cells(“Width”).FormulaU = “=Height * ” & flagAspectRatio
Else
‘Maximise width of flag
flagShp.Cells(“Width”).FormulaU = “=” & subshp.NameID & “!Width”
flagShp.Cells(“Height”).FormulaU = “=Width / ” & flagAspectRatio
End If
flagShp.Cells(“PinY”).FormulaU = “=” & subshp.NameID & “!Height*0.5”
flagShp.Cells(“PinX”).FormulaU = “=” & subshp.NameID & “!Width*0.5”
‘Reset the group lock
subshp.Cells(“LockGroup”).FormulaU = 1
If Len(Dir(filePath)) > 0 Then
‘Clear the temp file
Kill filePath
End If
End If
End If
End If
flagExists = True
Exit For
End If
Next
End If
End If
End If
If flagExists = True Then
‘Move on to the next shape in the selection
Exit For
End If
Next subshp
End If
Next shp
End Sub
Private Function getLookup(ByVal value As String, ByVal aryIn As Variant) As Integer
Dim ary() As String
ary() = aryIn
Dim i As Integer
Dim found As Boolean
For i = 0 To UBound(ary)
If UCase(value) = UCase(ary(i)) Then
found = True
Exit For
End If
Next
If found = True Then
getLookup = i
Else
getLookup = -1
End If
End Function
Note : The following web site was invaluable for helping me download the flag image files from the CIA web-site : http://www.vb-helper.com/howto_download_url_to_file.html
Interesting related blog: http://dbwhisperer.blogspot.com/2008/07/getting-geography-data-from-visio.html
Download files : FlagsOfTheWorld.zip