Results 1 to 7 of 7

Thread: Permission denied ERROR 70 If I wait a few seconds

  1. #1

    Question Permission denied ERROR 70 If I wait a few seconds



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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 by Tangjoc; 2013-01-09 at 11:45 PM.

  3. #3
    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/inves...s?symbol=CA%3aCS"
    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 by Tangjoc; 2013-01-10 at 01:11 AM.

  4. #4
    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 by Tangjoc; 2013-01-10 at 01:29 AM.

  5. #5
    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

  6. #6
    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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •