How to automatically update excel from a web page

[petertaylor123]

Hey everyone,

I know this has been asked in the past, but I don't really understand some of the responses/solutions (I am a self confessed numpty).

I'm running Excel 2011 (on Mac OSX 10.6.8) and Safari 5.1

Basically, I am running spreadsheets for the UK premiership football league and aviva UK premiership rugby union leagues.

The SS I have created have got columns for player names, position, number of starting appearances, number of substitute appearances, total played minutes and number of goals/trys scored for this season.
It also has this same information on historical seasons, going back to 2001 where available (it took many hours of data inputting and web page trawling,,,,,)

They are now up to date, however it takes me a few hours to update them every week, manually.

I am sure that there should be a way for the SS to auto-update from the information posted on the respective League's website.
Am I correct or is this just not possible?
If so, how do would I go about importing this data?
Do I need a software programme?
I have had a play around with;
File>Import and
Data>External Data Sources
But haven't yet got anywhere.

I've seen responses on here (to I think similar queries) with 2000 and XP excel,
http://www.mrexcel.com/forum/showthread.php?t=50773
although Mr Juan Pablo Gonzalez presents a solution, I don't get it. At all.

I hope that I have made it clear what I am trying to achieve. If I haven't then please ask for clarification.
Thanks for your time and any help would be greatly appreciated.
Pete

 

[John_w]

Have you tried retrieving the data with a manual web query? I don't have a Mac so have never created an Excel Mac web query, and don't know if they can be created and controlled with VBA, but I found this MS article which explains how to create one - http://support.microsoft.com/kb/178870. That page was linked from http://forums.webtrends.com/webtrends/topics/better_mac_integration_with_office_2011?from_gsfn=true.

 

[petertaylor123]

thanks for the reply.
I had previously seen the first link that you posted.

But I couldn't work out how to construct the correct query.


I tried to follow the example given, but where the 2 systems differed, I think I kind of lost it.
I'll keep trying and playing with it,,,,,

Many thanks.
P

 

[ScottR]

Hi Peter

There is a simple way to retrieve all data from a web page but it is returned in HTML code and requires parsing into usable information. This has become my preferred methods of querying the web over time.

It may depend on what you end up getting but it is worth a try

Function WebData(mURL As String) As String

Dim objXMLHTTP, xml
Set xml = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xml.Open "GET", mURL, False
xml.send ""
WebData = xml.ResponseText

End Function