Excel VBA Web Scraping - Automation Error/Unspecified Error

Question

I'm trying to scrape and reformat some data in HTML. This is something I've done before with little problem but I'm running into an error.

Tagging ".net" just because there's a lot of overlap with error messages and resolutions.

The simplest case which produces it:

Sub main()

    Dim ie As InternetExplorer
    Dim htmlDoc As HTMLDocument

    Set ie = New InternetExplorer

    ie.Visible = True

    ' Unfortunately I'm not able to post the true link.
    ' But it is on github in case that's relevant.

    ie.navigate "https://pages.github.XXXX.net/XXXXX/XXX-XXXXXX/dev/XXXXXX/data/full/XXXXXX.html"

    Do While ie.Busy
        DoEvents
    Loop

    Set htmlDoc = ie.document

End Sub

Which throws back "run-time error '-2147467259 (80004005)': Automation Error. Unspecified Error". This error occurs with this line "Set htmlDoc = ie.document". It was also happening when I had the wait condition as "Do while ie.busy or ie.readyState <> readyStateComplete"

However, using just any old link it does work.

Sub main()

    Dim ie As InternetExplorer
    Dim htmlDoc As HTMLDocument

    Set ie = New InternetExplorer

    ie.Visible = True

    ' Unfortunately I'm not able to post the true link.
    ' But it is on github in case that's relevant.

    ie.navigate "https://stackoverflow.com/"

    Do While ie.Busy
        DoEvents
    Loop

    Set htmlDoc = ie.document

End Sub

When I pint out the readyState of the github instance it comes out '0', if that's informative. From other issues that I've read it seems that the problem is that the InternetExplorer object has been closed, but I'm not sure how that's happening.

It's been recommended elsewhere for this issue to "clean" the project (cut out all the code, save, put it back) but this didn't work for me.

Any thoughts?

you want a ready state of 4 I think to indicate page is loaded. Is this saying page is not loaded for some reason? Have you tried accessing the URL by any other methods? XHR? Selenium? I take it you have also been closing IE instances with .Quit or task manager? — QHarr, Jun 22 '18 at 19:27
@QHarr I haven't been explicitly closing the IE instances, just resetting. I also haven't tried accessing the page any other way. The only reason I'm using VBA is because I'm stunted with tools at the moment. I did just remember that PowerShell exists though, so I'll give that a shot. — Error_2646, Jun 22 '18 at 19:35
@QHarr Just after the hitting "reset" before running after the failure. — Error_2646, Jun 22 '18 at 19:41
Well, you still seem to have other URLs working but it is worth remember IE.Quit at the end of scripts. — QHarr, Jun 22 '18 at 19:43
@QHarr Thanks, this is something I only have to do once in a blue moon so I don't know the best practices. I am able to the url and scrape the data with Powershell. It's more of a headache but worst case scenario I can just do it with PS. — Error_2646, Jun 22 '18 at 20:11
@QHarr Linked to the solution if you were curious .... lotta time spent on a 6 keystroke fix. — Error_2646, Jun 25 '18 at 19:41

score 2 · Accepted Answer · answered Jun 25 '18 at 19:42

The answer was found here: Excel VBA Controlling IE local intranet

The github link is local intranet with more strict security. Internet explorer instances apparently are finicky in that case.

So I needed to use

Set ie = New InternetExplorerMedium

instead of

Set ie = New InternetExplorer

Excel VBA Web Scraping - Automation Error/Unspecified Error

1 Answers1