IE-Automation: Stuck with debuging only first instance of loop is done correctly, but loop does not break

Question

I have written code which navigates to websites goes to inner page expands details and then copies HTML tables to newly created sheet.

Navigation loop is without issue it loops correctly through all elements and expands details correctly.

The problem rises with copying tables. On first time tables are copied when it comes to second round only new sheet is created but no content is pasted.

I debug.print content of variable and also used break point so data is in variable but it's not pasted.

If you need more data please let me know. Thank you in advance!

For Each ele In html.getElementsByTagName("a")
If InStr(1, ele.ID, "cup_cuplv", vbTextCompare) > 0 Then

ele.FireEvent "onclick" ' clicks on an item in the list

PauseTime = 5 ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

       ' For Each ele1 In html.getElementsByClassName("icn_vw icn_tgld")

           ' If InStr(1, ele1.innerText, "Details", vbTextCompare) > 0 Then
            Set ele1 = html.getElementsByClassName("icn_vw icn_tgld")(0)

            ele1.FireEvent "onclick" ' clicks to expand details

            Set ele1 = Nothing
            PauseTime = 10 ' Set duration in seconds
            Start = Timer ' Set start time.
            Do While Timer < Start + PauseTime
            DoEvents ' Yield to other processes.
            Loop

                Dim objTable As Object
                Dim lRow   As Long
                Dim lngTable As Long
                Dim lngRow As Long
                Dim lngCol As Long
                Dim ActRw  As Long
                'Set html = New HTMLDocument

                Set NewWS = ThisWorkbook.Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet) 'created new sheet
                Set IE = objShell.Windows(IE_count - 1)
                    Do While IE.Busy: Loop
                       html.body.innerHTML = IE.Document.body.innerHTML
                        With html.body
                            Set objTable = .getElementsByTagName("table")
                            For lngTable = 0 To objTable.Length - 1
                                For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                                    For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                                    Worksheets(NewWS).Activate
                                    NewWS.Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText ' problem here doesnt paste 2nd 3rd and nth time
                                    Next lngCol
                                Next lngRow
                                ActRw = ActRw + objTable(lngTable).Rows.Length + 1
                            Next lngTable

                        End With
                'Set html.body = Nothing
                Set objTable = Nothing
                Set NewWS = Nothing
                'End If

            PauseTime = 3 ' Set duration in seconds
            Start = Timer ' Set start time.
            Do While Timer < Start + PauseTime
            DoEvents ' Yield to other processes.
            Loop
        Debug.Print ele1.ID
        'Next ele1



    Debug.Print ele.ID
html.getElementById("tab_mn2").FireEvent "onclick" ' goes back to main item list
PauseTime = 5 ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
End If
Next ele ' goes to next item in main list here

I notice you never reset the variable `ActRw`, which means you start copying to row 1 of the first worksheet you create, then the second worksheet you create you start your copying at the row after the one you finished the first sheet at. So are you sure you aren't writing anything to the second sheet - or are you just writing it at row 100 or something? — YowE3K, Jul 21 '17 at 09:47
YES! You are correct about ActRw! Was going nuts :) thanks again — Petras K, Jul 21 '17 at 10:04
I posted as answer, including a comment about the scope of variables within VBA (just in case you are used to .Net scope) — YowE3K, Jul 21 '17 at 10:25

score 1 · Accepted Answer · answered Jul 21 '17 at 10:24

I notice you never reset the variable ActRw, which means you start copying to row 1 of the first worksheet you create, then the second worksheet you create you start your copying at the row after the one you finished the first sheet at.

So are you sure you aren't writing anything to the second sheet - or are you just writing it at row 100 or something?

I believe it will probably work if you include a statement saying

ActRw = 0

after the line where you say

Dim ActRw  As Long

Note: VBA does not have variable scope any smaller than procedure level. So, although VB.Net (and possibly other languages) would interpret a Dim ActRw As Long statement within a Loop block as creating a new variable each time through the loop, and thus initialising it to zero each time through the loop, VBA creates the variable once as soon as the procedure commences.

score 0 · Answer 2 · answered Jul 21 '17 at 09:44

0

Can you move this code inside the second loop.

Set NewWS = ThisWorkbook.Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet) 'created new sheet

Also make sure to rename this sheet at the end of the loop for avoid duplication of the sheet name

answered Jul 21 '17 at 09:44

nishit dey

458
1
7
21

@YowE3K comment is correct answer ActRw should have been reseted. – Petras K Jul 21 '17 at 10:09

IE-Automation: Stuck with debuging only first instance of loop is done correctly, but loop does not break

2 Answers2