UEFA Team Logos and Country Flags In Excel

Just in time for the Champions League quarterfinals, here's another frivolous spreadsheet that pulls images from UEFA.com into Excel.

I could think of a few places where this would be useful:

• The Excely Champions League spreadsheet
• Excel Template
• Michael Wray's stellar work

In fact, if any of these guys are reading this, feel free to adapt this technique! I will happily accept credit for it.

UEFA image URLs

On the UEFA website the images come in several different sizes and the URLs are formatted like this:

Team Logos:

• http://img.uefa.com/imgml/TP/teams/logos/18×18/50080.png
• http://img.uefa.com/imgml/TP/teams/logos/32×32/50080.png
• http://img.uefa.com/imgml/TP/teams/logos/70×70/50080.png

Country Flags:

• http://img.uefa.com/imgml/flags/18×18/ESP.png
• http://img.uefa.com/imgml/flags/25×25/ESP.png

As you can see, there is a base URL that has a static pattern, while the logo size and team code are the only parts that change.

Team Logo URLs

The team logos come in three sizes: 18×18, 32×32 and 70×70.

Unfortunately, the team logo codes do not follow any kind of naming scheme; I had to right-click each image to find out it's filename, so I decided to focus only on the quarterfinal teams. If you wanted a complete listing, you'd have to research each one.

• Chelsea — 52914.png
• Man Utd — 52682.png
• Inter Milan — 50138.png
• Schalke 04 — 57388.png
• Madrid — 50051.png
• Tottenham — 1652.png
• Barcelona — 50080.png
• Shakhtar — 52707.png

Now that I have the teams, all we need to do is specify the image size and the team code in order to locate the appropriate image.

Since the images are all PNG files, I won't even bother downloading them — WebBrowser Controls will be used to display each image.

Country Flag URLs

The country flags are round and come in two sizes, 18×18 and 25×25. (If you can find others, I'd be glad to hear it!) Like the team logos, they don't follow a strict naming scheme. We're just going to follow how UEFA names each image.

All we need to do is map the team name to their country (using two corresponding arrays) to figure out which image to display.

• Chelsea — ENG.png
• Man Utd — ENG.png
• Inter Milan — ITA.png
• Schalke 04 — GER.png
• Madrid — ESP.png
• Tottenham — ENG.png
• Barcelona — ESP.png
• Shakhtar — UKR.png

Arrays

So I'll need three arrays: one to represent the teams, one for their logo codes, and one for their flag names. The arrays should correspond, so if I know the position of an element in one array I know the other two values at the same position in the other arrays will match what we've already discovered.

Public Const TEAMNAMES As String = "Chelsea," & _
       "Manchester United," & _
       "Internazionale Milan," & _
       "FC Schalke 04," & _
       "Real Madrid," & _
       "Tottenham Hotspur," & _
       "FC Barcelona," & _
       "Shakhtar Donetsk"

Public Const TEAM_LOGO_CODES As String = "52914," & _
       "52682," & _
       "50138," & _
       "57388," & _
       "50051," & _
       "1652," & _
       "50080," & _
       "52707"

Public Const COUNTRYNAMES As String = "ENG," & _
       "ENG," & _
       "ITA," & _
       "GER," & _
       "ESP," & _
       "ENG," & _
       "ESP," & _
       "UKR"

With this information, I can verify that Chelsea (position 0 in TEAMNAMES array) is 52914.png (position 0 in TEAM_LOGO_CODES array) and also ENG.png (position 0 in COUNTRYNAMES array). I smell a userform coming!

Sample Userform

And that's just what I've done. I created a sample userform that takes the team name selected from a listbox and builds the appropriate image URLs for the team logo and country flag.

Download UEFA Images for Excel 2000-2003
Download UEFA Images for Excel 2007

After downloading the file, open it and run the StartForm procedure to run the userform.

Adding More Teams

Updating the file to include more teams is devastatingly simple. Just add the team name to the first constant (TEAMNAMES), then the corresponding logo code (right-click the image to get its filename) and country abbreviation (per UEFA). The update will cascade into the userform.