I'm Stumped with VBA & XML

[Dennis G]

Hi All,
I'm pulling the hair out and have googled myself silly, but I just can't work this out. I'm hoping Shaun or one of the other Excel experts can help me.

On Sheet2 Cells A3-A10 I have a list of URLs representing one meeting.

A3 =

h**p//tatts.com/pagedata/racing/2013/2/9/BR1.XML
etc

The macro I'm having hassles with -



Sub LoadRaceDetails()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False

xmldoc.Load ("h**p//tatts.com/pagedata/racing/2013/2/9/BR1.XML")





What I'm trying to achieve is to replace the URL (in blue) with code that will read Sheet2 Cell A3 and then loop to read Cell A4 etc - I'm pretty sure I can handle the looping.....
This XML stuff is doing my head in....

Thanks,
Den

[stugots]

not sure on the looping but this will pull the address from A3



Address = Range("A3")

xmldoc.Load Address

[Dennis G]

Excellent, stugots.
Works brilliantly.

For the rest I altered the code to -

A = 3
Address = Sheets("Sheet2").Cells(A, 1)

xmldoc.Load Address


then at the end of the macro - A = A + 1
so that the next pass A = 4 ie. Cell(A4)

Thanks again, mate
Den

[stugots]

No prob Dennis

[moeee]

Ain't it great when the answer was so simple , yet achieved awsome results for the member.

[Dennis G]

Ain't it great when the answer was so simple , yet achieved awsome results for the member.It sure is mate. I was using web query to download Tatts results, but lately it has taken up to 45 mins to do all meetings on a big saturday.

I've just finished this workbook (bugs not withstanding) just ran it for last saturday and was done in under three minutes.....

Some tweaking to do (not dealing with abandoned meetings too well and some other odd results) but will get there...

[moeee]

Good for you Dennis G
Is that Program adaptable to SuperTab or does the Flash mess it up?

[Dennis G]

Good for you Dennis G
Is that Program adaptable to SuperTab or does the Flash mess it up?I think you're correct about Flash, this only works with XML feeds.

Anyway here it is for your pleasure....

[stugots]

Nice one Dennis, if I could make one suggestion, adding the line -

Application.ScreenUpdating = False

as the first line TodaysFields macro speeds it up considerably.


Would also add -

Application.ScreenUpdating = True

as the last line.

[Dennis G]

Thanks, stugots, that helped a bit.
I'm still in awe of downloading all races on last MC day (107 races) in under 3 minutes.....

[Dennis G]

version 2 is completed -

* results are now numericals
* win divs ranked
* scratchings fixed
* abandoned races noted
* blank row between venues for easier cut/paste
* venue list displayed on front page

[stugots]

Hi Dennis, if you move the screenupdating line right to the end as below the code runs much quicker, for example yesterdays results completes in 20 seconds.



TransposeRaceCodes
Application.ScreenUpdating = True
End Sub

[Dennis G]

Thanks mate, flying now...

A fix for abandoned - in the LoadRaceDetails macro two thirds of the way down after ' Abandoned the 9 should be replaced with a 4.

"=IF(RC[-5]<>"""",IF(R1C[9]=""ABANDONED"",""ABD"",""""),"""")"

should be -

"=IF(RC[-5]<>"""",IF(R1C[4]=""ABANDONED"",""ABD"",""""),"""")"


I'm using my laptop and quite often forget the numlock key and when I type a number it doesn't appear :-[

[aussielongboat]

attached is the one i use.
just add the date in sheet 3 b1,c1,&d1 and some field info comes out in sheet 1 and the results in sheet 4.

the choose the "both for date" button

update as you wish

good luck
aussie