Same VBA code in the same workbook works on one computer but not the next.

[Oliver Dewar]

Hi Guys.

I have two computers at home. One is my every day workhorse. One is for 'automation' tasks.

I have a macro I've written which opens internet explorer to a url then brings the innerhtml content into a sheet separated by < so that I may process each tag.

On my workhorse PC everything works perfectly but on my automation PC, the exact same workbook bugs out. Both run office 2007 and windows 7.

Here's the guts of the code I'm using without the specific website involved for privacy.

Dim IE As Object
Dim x As Variant
Dim a As Integer

With IE
        .Visible = True
        .Navigate "http://example.com"
Do Until .readyState = 4: DoEvents: Loop
End With


With IE
x = Split(.document.body.innerhtml, "<")
For a = 0 To UBound(x)
Cells(a + 1, 1).Value = x(a)
Next a
End With

The error on the automation PC always occurs on the 'Cells(a + 1, 1).Value = x(a)' line and the error message reads:

"Run-time error '1004':
Application-defined or object-defined error"

I should note that internet explorer opens and navigates fine before this error is encountered and also that the files are the same file exactly on both computers as they are in cloud storage.

Any ideas what's going wrong here?

 

[RickXL]

Hi,

Difficult to say.

However, Cells is not qualified. That is the sheet is not specified so Excel will make some assumptions about which sheet to use. Try qualifying it with the sheet name. For instance:

Sheet1.Cells(a + 1, 1).Value = x(a)
or
Worksheets("mySheet").Cells(a + 1, 1).Value = x(a)

It may just be that the wrong sheet was activated when the macro got to that line.

 

[Oliver Dewar]

Thanks Rick.

I tried your suggestion and it didn't fix it.

However I have just noticed something new.

The code doesn't bug out on the first time through that line of code... it bugs on the 2405th time. So perhaps this is a memory issue?

It works perfectly 2.5k times then dies.

Any ideas of how I might be able to lighten the memory load here? I was thinking maybe write 'innerhtml' to a text file first then query that rather than holding the entire string in the memory. Might that work? Or is there a more simple and elegant way?

 

[RickXL]

Interesting.

There is another way to do this.
First, you don't need to use Internet Explorer. You can use Microsoft XML instead.
Second, there are ways to parse HTML using the Microsoft HTML Object Library.
You need to select both in Tools-->References.

Here is a sample bit of code showing both. I don't think it is entirely mine but I can't remember where it came from. Google will find you lots of similar examples.
The XML part just works instead of IE but the parsing part I always find difficult. Basically you can look for tags or tables etc in the HTML and navigate to objects like that.

Sub AAA()
    Dim mXML As MSXML2.XMLHTTP: Set mXML = New MSXML2.XMLHTTP
    Dim O As MSHTML.HTMLDocument: Set O = New HTMLDocument
    Dim Coll As HTMLDivElement
    Dim p
    Dim k As Integer
    With mXML
        .Open "GET", "http://uk.soccerway.com/matches/2014/02/23/england/premier-league/liverpool-fc/swansea-city-afc/1483721/?ICID=HP_MS_01_01", False
        .send
        Do: DoEvents: Loop Until .ReadyState = 4
        O.body.innerHTML = .responseText
    End With
    Set Coll = O.getElementById("page_match_1_block_match_additional_info_6")
    For k = 0 To Coll.getElementsByTagName("DT").Length - 1
        Range("A" & k + 1) = Coll.getElementsByTagName("DT")(k).innerText
    Next
    For k = 0 To Coll.getElementsByTagName("DD").Length - 1
        p = Split(Coll.getElementsByTagName("DD")(k).innerText, vbLf)
        Range("B" & k + 1).Resize(1, UBound(p) + 1).Value = p
    Next
End Sub

 

[Oliver Dewar]

Thanks so much Rick.

I started to work with your code and it didn't work straight off... but the ideas in it lead me to find this code below... which I have now integrated into my original code and it's working perfectly on both PCs now! Success. Thanks mate.

Sub webpage()


Dim internet As InternetExplorer


Dim internetdata As HTMLDocument


Dim internetlink As Object


Dim internetinnerlink As Object


Set internet = CreateObject("InternetExplorer.Application")


internet.Visible = True


internet.Navigate ("URL")


Do While internet.Busy


  DoEvents


Loop


Do Until internet.ReadyState = READYSTATE_COMPLETE


  DoEvents


Loop


Set internetdata = internet.Document


Set internetlink = internetdata.getElementsByTagName("a")


i = 1


For Each internetinnerlink In internetlink


ActiveSheet.Cells(i, 2) = internetinnerlink.href


i = i + 1


Next internetinnerlink


End Sub

 

[RickXL]

No problem and thanks for the feedback.

I am glad you found a solution.