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?
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.