Results 1 to 3 of 3
  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
    I am a novice at this, but have developed an Excel application that works its way through about 300+ links obtained from a web page.
    The Excel Query first obtains the parent web page in a worksheet, then works its way down the set of 300+ cells containing a link.

    For some months I have been plagued by a run-time error '1004', and while I'm not sure that my solution is the best, it does seem to have avoided the 1004 error.

    The clue was : in an unattended loop, some links gave a 1004 error and that specific link was skipped, yet if I single-stepped through the code (or set a breakpoint just before re-issuing the web query), everything ran perfectly.

    I tried an Application.Wait (2 seconds) without success.
    I tried variations on the settings within the web query (Background=True/false etc.) without success.

    Then I tried randomizing what appears to me to be a user-id, and the routine (attached) now retrieves every link without a 1004 error.
    Code:
    '        .Name = "Render&c=Page&cid=971188561551"
            .Name = "Render&c=Page&cid=87118856155" & Right(Str(Rnd), 1)
    It is as if I am limited in the number of "cid=" accesses I can make within a time interval. Randomizing the cid= seems to make me appear as a different user between successive retrievals.

    And yes, perhaps I should loop until cidOld <> cidRandom ...
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quick comment on your code: You keep adding new querytables to your sheet.
    Instead, use ONE querytable and change it's connection string.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    Instead, use ONE querytable and change it's connection string.
    Thanks, Jan Karel; You are correct, and you can see my clean-up loop just ahead of the code.
    I am afraid I am still in the trembling migration from the recorded macro version; hence the ugly code. It isn't well thought-out at all.
    Nonetheless I'll make the change, for what's the point of harboring known lousy code while complaining that it's not working well, right?

Posting Permissions

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