Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Large web query question (2003 sp3)

    New to web query
    I have a project that requires a lot of web tables to be query from Yahoo (Finance stuff) approx 1000
    I have been using the web query an dl directly while roughing out the project ...
    What's the easiest way to dl many tables (Web pages) then import them into excel
    Guessing theirs a program out there to help with this ... web pages to cvs then import into excel or something like that
    or is it ultimately easier to do web query direct to excel

    Any help/ideas would be appreciated ... Thanks in advance

    SirBogs

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Large web query question (2003 sp3)

    Welcome to Woody's Lounge!

    Once you've set up a web query in Excel, you can set it to update itself automatically at a specified interval, or when you open the workbook.

    INEXP Software - Web Table Extractor is a fairly cheap shareware utility. (I don't have any experience with it myself)

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large web query question (2003 sp3)

    Thanks the achieves are wonderful source of info :-)

    I failed to mention i will be wanting to update the spreadsheet data and the web table extractor does not meet that requirement
    I am concerned about over loading excel with web query's ... Are there any general guidelines as far as to how many query's or file size ....
    Is their an issue if the web page layout changing over time I'm sure it will?

    Thanks
    SirBogs

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Large web query question (2003 sp3)

    You mentioned that you wanted to load 1,000 tables or so. With such a large number, I wouldn't let them update automatically. You could write a macro that updates each in turn, with refreshing in the background turned off, so that they won't update simultaneously (which would overburden your internet connection):

    Sub UpdateAllQueries()
    Dim wsh As Worksheet
    Dim qtb As QueryTable
    For Each wsh In ActiveWorkbook.Worksheets
    For Each qtb In wsh.QueryTables
    qtb.Refresh BackgroundQuery:=False
    Next qtb
    Next wsh
    End Sub

    I think a web query will automatically add/remove columns if the layout changes.
    You can specify how to handle added/deleted rows in the query's properties.

  5. #5
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large web query question (2003 sp3)

    Nice little macro ... works great
    I agree this should take care of the query updateing issue

    Appreciate the advice ... Thanks
    Sir Bogs

Posting Permissions

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