Excel web query can it be automated ?

[Captain Sensible]

Managed to get it working using

Dim qry As QueryTable
With Sheets("Sheet2")
Set qry = .QueryTables.Add(Connection:="URL;http://localhost/sp/scrape.php?url=" & Sheets("Sheet1").Range("A10").Text, Destination:=.Range("A1"))
qry.Refresh
End With


Hope that help to anyone else who needs it.

Thanks to all that replied

[tkp]

Hi Captain - so what you've done is used a php screen scraper on (i'm guessing at) IIS on your machine to grab some data from a website, parse it and then put the results into excel via the excel query.

That's pretty cool.

Do you refine the data once in excel ?

With PHP can you fill in forms so that you can go to a php (or other script) generated page - I've only ever managed to scrape from static websites ?

[Captain Sensible]

tkp I'm using xampp which instals apache as my server. When i originally looked at coding all the microsoft stuff had to be paid for ,asp, IIS etc so I used php apache and mysql which were all free open source and thousands of code snippets out there.

I don't do much with the data once it's in excel except compare it against the betfair odds. The php does all the parsing and just displays the current price in decimal odds and runner details which can be matched using vlookup.

I've never had any problems scraping php or asp pages etc as once they've been rendered on the other server they're effectively just html. Sometimes requests can be blocked if they don't appear to be coming a browser but that can usually be faked by using headers. Although it's been a long time since I've had to do that and haven't had a page I couldn't scrape using file_get_contents for some years now.