VBA and Excel: sporadic error 91 in grasping huge quantity of datas from the web

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
526
Office Version
  1. 2007
Hello everybody.

I've been grasping a lot of data from a website via Excel using the following code.


Code:
                Dim tbls, tbl, trs, tr, tds, td, R, c
                    Set tbls = IE.document.getElementsByTagName("table")
                        For R = 0 To tbls.Length - 1
                        Debug.Print R, tbls(R).Rows.Length
                        Next R
    
                Set tbl = IE.document.getElementsByTagName("table")(47)
                
                Set trs = tbl.getElementsByTagName("tr")

                For R = 0 To trs.Length - 1
                    Set tds = trs(R).getElementsByTagName("td")
                        If tds.Length = 0 Then Set tds = trs(R).getElementsByTagName("th")
                            For c = 0 To tds.Length - 1
                                ThisWorkbook.Worksheets("Sheet1").Range("A1").Offset(R, c).Value = CleanAll(tds(c).innerText)
                            Next c
                Next R

The operation is usually repeated hundreds of times with no problems.

But sometimes it bumps into an error
Run-Time Error 91 Object Variable or With Block Variable Not Set
usually on row
Code:
ThisWorkbook.Worksheets("Sheet1").Range("A1").Offset(R, c).Value = CleanAll(tds(c).innerText)
with a complete stop of the task.

Now, with the high volume of data I deal with it is a loss of time to restart the process, and it is not a problem if only one page is not grasped.

How could I instruct the macro to ignore the error and keep on?

Thank's.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How could I instruct the macro to ignore the error and keep on?

Code:
[COLOR=#008000]'To ignore errors[/COLOR]
On error resume next

[COLOR=#008000]'Your code[/COLOR]
ThisWorkbook.Worksheets("Sheet1").Range("A1").Offset(R, c).Value = CleanAll(tds(c).innerText)

[COLOR=#008000]'To get back to normal and recieve error messages[/COLOR]
On error goto 0
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top