Sky Sports has some nice images for different soccer teams and leagues in Europe, so I put together a spreadsheet that downloads and displays the images on a userform.
It also checks FoxSports.com for league news (via RSS). Double click on a news item to visit FoxSports.com for the full story.
Feed items are cached in the local temp folder, but you can force the form to requery FoxSports.com by checking the "Always refresh headlines" checkbox. Personally I don't recommend this (the box is unchecked by default) because new news items don't appear that often, so there's no need to use so much bandwidth by pinging their RSS feed constantly.
When a league is selected, the league country's flag and league logo are downloaded from FIFA.com and SkySports.com respectively. Since these are GIF images, they are downloaded and displayed in Image controls. The URLDownloadToFile Windows API is used to download the image by constructing the appropriate URL. Here is the download code:
Function DownloadFile(URL As String, Optional destinationFolder As String) As Boolean
Dim destFolder As String
Dim fileName As String
' check for valid URL (in case Sky changes their website)
If Not IsValidURL(URL) Then
Exit Function
End If
' check for valid folder
If Len(destinationFolder) = 0 Then ' not specified, use default
destFolder = Environ("Temp") & Application.PathSeparator
Else ' specified folder
If Len(Dir(destinationFolder)) = 0 Then ' bad folder, use default
destFolder = Environ("Temp") & Application.PathSeparator
Else
destFolder = destinationFolder
End If
End If
fileName = GetFileName(URL)
DownloadFile = (URLDownloadToFile(0, URL, destFolder & fileName, 0, 0) = 0)
End Function
The destination for the downloaded images is the local temp folder by default, but you can specify any folder you like.
When a team is selected, the team's logo is fetched from Sky Sports' website. Since they are PNG files, they must be displayed using a WebBrowser control instead of an Image control. You can choose from three different image sizes.
There is some delay built into the userform, to check if the image URLs are valid. That functionality is provided by the function found at Validate URL. Feel free to remove it.
News feed leads to full story
When we fetch feed items from SkySports.com, we load the results into a two-dimensional array consisting of the headline and the link to the full story. The array is then loaded into a listbox on the form. However only the first dimension of the array (the headline) is made visible, because the ListBox.ColumnCount property is set to 2 but the ListBox.ColumnWidths property is set to ";0 pt" which effectively hides the second column. The second dimension of the array is only used when the listbox is double-clicked. So it is very simple to allow the end user to visit SkySports.com to read the full story when double-clicking a headline:
Private Sub NewsItems_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo ErrorHandler
If Me.NewsItems.ListCount > 0 Then
' second column is link to the full story
ActiveWorkbook.FollowHyperlink Me.NewsItems.Column(1)
End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
I originally intended to display the Fox Soccer logo on the form, but couldn't find an image of a suitable size that was also a GIF or JPG; displaying the logo in an ugly WebBrowser control (they ARE ugly with that scrollbar on the side) wasn't an option.
Download Soccer News Spreadsheet for Excel 2003
Download Soccer News Spreadsheet for Excel 2007/2010
After downloading the workbook, run the StartForm procedure to run the userform.
Disclaimer: This spreadsheet is not endorsed by, or affiliated with, Fox Sports or Sky Sports or FIFA. See Disclaimer for more information.
