import table from website

  • I'm trying to import information from a website. I think I know how to do what I want to do, but the code works intermittently. I can run the macro 10 times back to back, and it import varying amounts of data each time before the error will come up. The error (Run-time error'91': Object variable or With block not set) seems to be related to

    Code
    Set objCollection = ie.document.getElementsByTagName("b")
        i = objCollection.Length - 1
        
        Range("B2").Value = objCollection(i).innerText


    Though if i comment out that particular section, the error just comes on the next object declaration. Any thoughts on why this would work sometimes but not others? The code I use to set up my Internet explorer object is as follows

    Code
    Set ie = CreateObject("InternetExplorer.Application"
        ie.Navigate "http://scores.covers.com/basketball-scores-matchups.aspx"
        Application.StatusBar = "Scores are loading. Please wait..."
        Do While ie.Busy
            Application.Wait DateAdd("s", 1, Now)
        Loop


    But I don't think thats the problem, that's worked for me for other things before. Let me know what you think

  • Re: import table from website


    Some websites will restrict a certain amount of hits from the same IP address in a given time period, it's quite possible the website you're scraping is doing this so actually there is nothing on the returned page, so there are no b tags on the page. This will cause your code to error in the way you are describing.


    I mean, why not just use, the import data from Web Wizard? It seems more efficient that automating Internet Explorer which is very slow

  • Re: import table from website


    I dont think its that, the number is random, and usually on the first pass through. I'm using internet explorer because i want to extract a variable number of tables representing game data for each day going back 5 years. I also need to pull data related from each game from three other pages per game. I'm not familiar with the web wizard, I just now looked over it. Is there a way to automate an import like that using the web wizard? I'm ok with the speed problems, I plan on letting it run overnight, or however long it takes

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!