Results 1 to 4 of 4

How to automatically update excel from a web page

This is a discussion on How to automatically update excel from a web page within the Excel Questions forums, part of the Question Forums category; Hey everyone, I know this has been asked in the past, but I don't really understand some of the responses/solutions ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Default How to automatically update excel from a web page

    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,
    Help importing data from a website into Excel?
    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

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,116

    Default Re: How to automatically update excel from a web page

    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/webtrend...from_gsfn=true.

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    11

    Default Re: How to automatically update excel from a web page

    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

  4. #4
    Board Regular
    Join Date
    Apr 2008
    Posts
    404

    Default Re: How to automatically update excel from a web page

    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

    Code:
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com