Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Web Query in Yahoo (Excel 2000, 2002, 2003)

    I'd appreciate confirmation of any part, or all, of my findings.

    I have two computers here, a Big Beige Box running XL 2002/XP, and a laptop running XL 2000 and XL 2003.

    Book1 has three sheets labeled Google, Yahoo and MSN for obvious reasons.
    Each sheet can display data from one of three companies KO, GE and GLW.
    The sole macro "Macro2" is supposed to cycle through the three companies on any one sheet. If you happen to be on the Google sheet, each run of Macro2 should show results for KO, then for GE, then for GLW, and back to KO, incessantly. (The three sheets are not synchronized, so you can end up with KO on the Yahoo sheet and GLW on the MSN sheet etc.)

    The connection string varies according to the web source - hence there are 3 (companies) multiplied by three (web source) combinations.
    BBB XL 2002: For each sheet, macro2 does what I expect it to do, and I see the $value cycle through 6,476 then 39,900 then 1,261 (approx) and back to 6,476
    LT XL 2000: For each sheet, macro2 does NOT what I expect it to do; I see the $value cycle through 6,476 then 39,900 then 1,261 (approx) and back to 6,476 EXCEPT FOR THE YAHOO SHEET.
    LT XL 2003: For each sheet, macro2 does NOT what I expect it to do; I see the $value cycle through 6,476 then 39,900 then 1,261 (approx) and back to 6,476 EXCEPT FOR THE YAHOO SHEET.

    At this stage, it seems to me that the Yahoo query will not refresh on the laptop in XL 2000 & XL2003, but will refresh on the Big Beige Box in XL2002.

    I flip between machines using a KVM switch with Scroll-Scroll-UpArrow - about 5 seconds total - and get the same baulky results on LT XL2003 for Yahoo, so it is not a time-of-day or 15-minute penalty issue.

    Am I going crazy? Is my logic correct?

    I have only one macro, and can duplicate these aberrant results at any time of day. It seems to me that XL2000 and XL2003 baulk at the Web Query refresh on Yahoo alone.

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

    Re: Web Query in Yahoo (Excel 2000, 2002, 2003)

    The Google and MSN sheets contain one querytable each.
    The Yahoo sheet contains two querytables. Your code loops through the querytables using For ... Next, but exits the loop after the first one, so the second querytable never gets modified.
    Try omitting the Exit For statements, and placing the wq.Refresh statement within the For ... Next loop.

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

    Re: Web Query in Yahoo (Excel 2000, 2002, 2003)

    BTW, you should delete the custom toolbar when the workbook is closed.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query in Yahoo (Excel 2000, 2002, 2003)

    >The Yahoo sheet contains two querytables.
    Hans, thanks for spotting this. I don't know whether I or the client caused this, but one of us deserves to have his hand slapped.

    I removed the second query and tried running again. I think I have resolved the problem of occasional loss of data to a timing problem.
    My code loops through all worksheets in a workbook, and where it finds a web query, rebuilds the conection string with a new corporate code and moves on.
    The automatic update seemed to get confused if I'd advanced to the next sheet and query before the previous query was downloaded.
    My new code looks something like this:<pre> qry.Connection = strConnection
    Do
    Dim blnRefresh As Boolean
    blnRefresh = qry.Refresh(False)
    Loop Until blnRefresh
    </pre>

    and that seems to make things work.


    >BTW, you should delete the custom toolbar when the workbook is closed.
    Thanks. I neglected to do so in my haste to assmeble a testbed.

Posting Permissions

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