Permission denied ERROR 70 If I wait a few seconds

Tangjoc

New member
Joined
Jan 9, 2013
Messages
11
Reaction score
0
Points
0
I'm getting tables from a website. I may have to wait for the site to open, I used Do While tbls(i) Is Nothing.
But if I get a permission denied, Is Nothing doesn't work.

I tried : If iserror(tbls(i)) Then, But if permission denied, iserror is not working either.

I know that if I could catch the error like : resume next and wait few second, It would work.
But I can not catch the error.

Any advise would be helpfull. Thank you

Sub Trouve()

With appIE
.Navigate sURL
Do While appIE.Busy: DoEvents: Loop
End With

Set doc = appIE.Document
Set tbls = doc.getElementsByTagName("TABLE")

If iserror(tbls(i)) Then
Application.Wait Now + TimeValue("00:00:10")
Set tbls = doc.getElementsByTagName("TABLE")
End If

Do While tbls(i) Is Nothing
Application.Wait Now + TimeValue("00:00:03")
Loop

GetTableData tbls(i), Worksheets("Work").Range("A" & Rows.Count).End(xlUp).Offset(2)

End Sub
 
I wish the answer could be some thing like :

1) If tbls(i) = permission denied OR IF error then ... I don't know how to write it

2) If tbls(i) = False then ... but tbls(i) is an Object; not a Boolan

3) Select case tbls
Case tbls(i) = something ????
Else case
Wait 10 seconds
It doesn't work

4) I'm not familiar with private Function
Does a private Function could grab the error ?
Or could we use a private Function or something to check if tbls(i) is ... or is not ... ?

Any suggestion is well come.

My programme works as is, but when I get the error, I have to restart the macro.

Thanks to all
 
Last edited:
Here is the all sub, in case you would like to try it.

Dim appIE As Object ' InternetExplorer.Application
Dim sURL As String
Dim Element As Object ' HTMLButtonElement
Dim btnInput As Object ' MSHTML.HTMLInputElement
Dim ElementCol As Object ' MSHTML.IHTMLElementCollection
Dim Link As Object ' MSHTML.HTMLAnchorElement
Dim tbls As Object ' tables collection
Dim i As Single
Dim y As Single

Sub GetTableData(ByRef tbl, rng As Range)
Dim cl As Object
Dim rw As Object
If tbl.Rows Is Nothing Then
i = i - 1
Exit Sub
End If
For Each rw In tbl.Rows
For Each cl In rw.Cells
rng.Value = cl.outerText
Set rng = rng.Offset(, 1)
Next cl
Set rng = Cells(rng.Row + 1, 1)
Next rw
End Sub

Sub Trouve()
y = 0
With appIE
.Navigate sURL
Do While appIE.Busy: DoEvents: Loop
End With

Set doc = appIE.Document
Set tbls = doc.getElementsByTagName("TABLE")
ErrHandler:
If ErrHandler Then
Application.Wait Now + TimeValue("00:00:10")
Set tbls = doc.getElementsByTagName("TABLE")
End If
Do While tbls(i) Is Nothing
On Error GoTo ErrHandler
Application.Wait Now + TimeValue("00:00:03")
y = y + 1
If y = 10 Then
i = i + 1
End If
Loop
If y <> 10 Then GetTableData tbls(i), Worksheets("Work").Range("A" & Rows.Count).End(xlUp).Offset(2)
End Sub

Sub SUMMARY()
Sheets("Work").Select
Set appIE = CreateObject("InternetExplorer.Application")
Worksheets("Work").Cells.Clear
sURL = "http://investing.money.msn.com/investments/financial-statements?symbol=CA:CS"
For i = 0 To 1
Trouve
Next i
appIE.Quit
End Sub

On error Go To ErrHandler does not work for error70

You start the macro SUMARY

I've haded :
ErrHandler:
If ErrHandler Then
Application.Wait Now + TimeValue("00:00:10")
Set tbls = doc.getElementsByTagName("TABLE")
End If

In the sub Trouve
But it doesn't work.

Try it, you will like it.
Don't forget to name one sheet = Work
 
Last edited:
If you try my subs, you will have to rewrite the link,
or take any link you would like if there is 2 tables in the site.

If you ever know of a better way to download tables from any site.
Please post it.
As I'm not a pro on VBA.
Thanks again
 
Last edited:
Hi all again

I've changed Do While appIE.Busy: DoEvents: Loop in the sub Trouve for
appIE.ReadyState <> 4: DoEvents:

I did not get the error70 since.

But since, I get, too often, <Object variable or With block variable not set>
for the tbl.row in the sub GetTableData. With On Error Resume Next right under, it doesn't work.

Any help would would be greatly appreciated.

Dim tbls as Object ' at the very begining
I thought a would change all the tbl for tbls, but I still get the error.

I will keep on trying other things.

Thank you
 
I think it works now.
I used both : Do While appIE.ReadyState <> 4 Or appIE.Busy: DoEvents: Loop

As for <Object variable or With block variable not set>
I get that when the table doesn't exist.

Whould any one know how to keep the programe runing if the table doesn't exist ?

Or would any body know how to count the tables on the site ?

I have a programe that can count the links
Here is:

Sub Findtable()
Set appIE = CreateObject("InternetExplorer.Application")
sURL = 'Any links
With appIE
.Navigate sURL
End With
Set doc = appIE.Document
Set tbls = doc.getElementsByTagName("TABLE")
' loop until the page finishes loading
Do While appIE.ReadyState <> 4 Or appIE.Busy: DoEvents: Loop

countLinks = appIE.Document.Links.Length
For y = 0 To countLinks - 1
currentText = appIE.Document.Links(y).innerText
If currentText = searchText Then
oldURL = appIE.LocationURL
appIE.Document.getElementsByTagName("a").Item(y).Click
End If
Next
End Sub

Used any Link where I wrote 'Any link.

I've tried to modified it to count the tables with out any succes.

Thank you all
 
Hi again
I'm trying to count the tables.
I just tried :
For Each tbls In appIE.Document.Links
c = c + 1
Next tbls
It counts something, I think it counts all objects or all the Links probably.
How could I count rows of the table(i) ?
Or how could I count the tables from the site ?
Or how could I exit the sub if the table doesn't exist ?
Any idea ?
Thank you
 
Back
Top