OK,

After wasting 4 frustrating days playing with Excel I finally worked it out. The secret ingredient was Excel's 'XML Maps'. I wish I found these things a long, long time ago.

If you know how to download the Raceday.xml file from the tatts.com site and save it to your local drive, you can kinda work it out from there (see the Excel example on the tatts.com site for more information, http://tatts.com/tattsbet_help/info/xml-excel-example)

What I did......
1) In cell A1 of Sheet 1, I entered '23/12/2012'
2) In cell A2 of Sheet 1, I entered this formula =text(a1,"YYYY/M/D")
3) In VBA Editor, I entered this code in the appropriate section of the macro.

'Example
'This is where the correctly formatted date is stored in Sheet1
RaceDate = Worksheets("Sheet1").Range("A2").Value

'This is the code that requests the xml file for the date displayed in cell A2
xmldoc.Load ("http://tatts.com/pagedata/racing/" & RaceDate & "/RaceDay.xml")

'Sample file response
'http://tatts.com/pagedata/racing/2012/12/23/RaceDay.xml

'This is the code to save it to your C:\Drive
xmldoc.Save ("C:\Raceday.xml")

If you then follow this well-explained tutorial for creating and using XML Maps, you should be right.

http://www.jkp-ads.com/articles/xmlandexcel05.asp

The xml elements in the Raceday.xml file that I found most useful for creating a race schedule were:
-MeetingCode
-RaceTime
-RaceNo
-VenueName
-MeetingType

NOTE: You only need to do this once. When you change the date in Sheet 1, you right click on the XML Map, and select 'XML > Refresh XML data' from the options and it will update the race list accordingly. I've tried it on a few Saturdays going back and it works well.


I hope I've explained this adequately. I will endeavour over the next few days to get a sample file up on here for people to play with...it won't be elegant like Shaun's code, but it will enough to get ya started and to help y'all stick it right up tab.com.au :-)