Excel web query can it be automated ?

[Captain Sensible]

My guess is yes but was hoping to avoid hours of work if someone has already written something similar. Basically I'm trying to set up an automated sheet that'll track the sporting life odds against the betfair shows. the url is in the format

http://localhost/sp/scrape.php?url=http ... _1640.html

and I'd want it to pick up the odds about a minute before the off.

I have a few snippets of code collected that'll run macros at preset times just need the mechanics of running the webquery. I guess I could just record a macro and then add that code to the script ?

I can add the day quite easily by sticking that within my php so that's not a problem and I guess I can strip the time and course from cell A1 and use either a lookup script against betfair abbreviations list or parse it using my php so gettung Bath 1st May - 16:40 5f Listed in the correct format shouldn't be a problem. Has anyone automated webqueries within excel ?

[Captain Sensible]

seems edit post has been taken away. So what I'm looking for is a way of passing cell A1 to a webquery about a minute from the off in the format http:/localhost/whatever.php?url=A1 where A1 contains the contents of that cell

[Mitch]

I'm trying to do a similar thing in VB.Net and I'm using http://www.sportinglife.com/racing/liveshows to strip all the url's for the days racing from.

I just need to find out how to send my info to Excel now.

I've never tried running a webquery from Excel other than a quick experiment when I had no idea what I was doing, and it seemed very slow.

[Mitch]

Out of curiosity, what is the localhost/scrape/php bit in the address all about? Is it quicker than using the address I mentioned?

[Mitch]

Damn the EDIT POST button being missing!! lol

What I meant by my first post was that you could get the list of races/url's from the address I mentioned into a seperate worksheet then run a macro to see which race is next and use the url direct from that rather than trying to construct it.

I hope that makes more sense to you than it does to me reading it back to myself!

[Ian]

Dunno if this helps or not but this code works for picking up a URL from col one in the active cell's row

MyURL = "URL;" & Cells(ActiveCell.Row, 1).Value

With Workbooks("????????").Sheets("???????")

With .Range("A1").QueryTable

.Connection = MyURL
.WebTables = "9"
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With

End With

[Captain Sensible]

Out of curiosity, what is the localhost/scrape/php bit in the address all about? Is it quicker than using the address I mentioned?

I used to use php for my own bots before BA came along so have a lot of scripts lying around - basically the http://localhost bit is just my own pc acting as a server to run php scripts. The script just parses the info stripping out everything other than the horse and odds. Plus it does little things like converting to decimal and removing apostrophes brackets etc so I can then use it alongside the BF odds using vlookup. I don't need to stirp the liveshow menu as the url's are always in the same format and should easily be recreated from the info in cell A1.

I don't find the webqueries that slow from sportinglife mitch maybe you should look at them again rather than using VB to cut and paste it. Soemtimes the SP server can be slow though and that maybe one of the reasons. I don't scrape it to often so no need for a quick refresh.

[Captain Sensible]

Grrr damn edit.....

Ian VB is all above me I'm afraid I just need something to pass the value in A1 to a web query , I can let my php script do the hard work of recreating the actual page to strip

http:/localhost/whatever.php?url=contents of A1

[GeorgeUK]

Captain, probably not what you're looking for, but 1 line of code can open the webpage in a new workbook.

workbooks.open Range("A1").text

This should open a new workbook, but also copies all the extra links and also graphics.
Can't test anything right now as work is starting to crack down on excessive internet use and monitoring webpages visited.

I don't know - some people... eh?

let me know if this is what you need, or if you are just looking for the table with the odds data.
I'll have a look tonight once i get home.

George

[tkp]

Captain - yes it can be automated.

Use the macro recorder to record you setting up a web query.

I've got a few I use to get tips from websites, strip out the time,meeting and then back or lay accordingly (or used to until the website changed format the other day!).

Automating the webquery is quite easy - it's the parsing which is a little more involved.

[Captain Sensible]

Luckily I've a bit of php knowledge so I route the sp page via that and everything is nicely parsed for me

I'll give it a go now before the next lot of racing starts - never seems to be enough time in the day at the moment.

[Captain Sensible]

I managed to automate this far but unable to enter the contents of A10 into the url by pasting when recording the macro. Does anyone with VB knowledge know how I can get the contents of A10 added to the line

"URL;http://localhost/sp/scrape.php?url=", Destination:=Range("A1"))

so it would read as something like

"URL;http://localhost/sp/scrape.php?url=Sedge 1st May - 17:45 2m4f Mdn Hrd", Destination:=Range("A1"))


Sub Macro4()
Range("A10").Select
Selection.Copy
Sheets("sp").Select
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://localhost/sp/scrape.php?url=", Destination:=Range("A1"))
.Name = "start"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.refresh BackgroundQuery:=False
End With
End Sub

[GeorgeUK]

"URL;http://localhost/sp/scrape.php?url=" & Range("A10").text & ", Destination:=Range("A1"))

[GeorgeUK]

oops (where is that darn edit button?)

not sure, but after the range A10 part, you may need to add in another inverted comma.
I'd walk through using F8 just to make sure.

With this data you're putting into A1, will it not overwrite the urls you have on the sheet?
Just thought i'd ask.

[Captain Sensible]

Thanks George I'll give it a go. They're aren't any url's stored on the sheet - The A10 and A1 refer to separate worksheets

Range("A10").Select
Selection.Copy
Sheets("sp").Select
Range("A1").Select