OZmium Sports Betting and Horse Racing Forums

Page 1 of 2

Show 40 posts from this thread on one page

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - I'm Stumped with VBA & XML (http://forums.ozmium.com.au/showthread.php?t=25984)

Dennis G

13th February 2013 10:03 AM

I'm Stumped with VBA & XML

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 -

Code:

 
 
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

13th February 2013 01:27 PM

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

Address = Range("A3")

xmldoc.Load Address

Dennis G

13th February 2013 02:17 PM

Excellent, stugots.
Works brilliantly.

For the rest I altered the code to -

Code:

 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

13th February 2013 03:26 PM

No prob Dennis

moeee

13th February 2013 03:58 PM

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

Dennis G

13th February 2013 08:01 PM

Quote:

Originally Posted by moeee

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

13th February 2013 08:17 PM

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

Dennis G

13th February 2013 11:00 PM

1 Attachment(s)

Quote:

Originally Posted by moeee

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

14th February 2013 08:26 AM

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

14th February 2013 11:46 AM

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.....

All times are GMT +10. The time now is 07:58 PM.

Page 1 of 2

Show 40 posts from this thread on one page