Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Inescapable code

    Excel 2003

    I run a regular routine which calls up an internet page and downloads a file. To anticipate problems I first check that there is an internet connection:
    If IsConnected = False Then
    MsgBox "Update failed: there is no Internet connection"
    Exit Sub
    End If
    Then get the file:
    ActiveWorkbook.FollowHyperlink "http://www. . . .
    It runs perfectly almost all of the time, but occasionally the website doesn't respond and the code just sits there waiting, without generating an error. I have to nuke Excel to get going again which is ugly !

    As a horrible workaround I save the file at the beginning of the code so I can recover to the exact point I need.

    Is there any way to either:

    1. Cause a timeout after a reasonable period ?
    2. Force an Exit on pressing a keystroke combination ?

    Or maybe there is some other way of addressing this ?

    Thanks

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Martin,

    Try adding something like this to your code.

    Code:
    Do
        DoEvents
    Loop Until .WebBrowser1.ReadyState = 4
    Have you tried Ctrl-Break?

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    It's hard to test for a specific site that does exist, but doesn't load within a specified time.
    But you might try something like this:
    Code:
    Sub gotoHyperlink()
    
    On Error Resume Next
    
    zLink = "http://www.bbc.co.uk"
    
    zSeconds = 5            '<< set the timeout value here
    
    'use Internet Explorer to navigate to the Link..
    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie                 'use shortcut
    .Silent = True          'Disable pop-up messages
    
    Application.StatusBar = "Navigating to: " & zLink & " ..."
    .Navigate zLink         'Navigate to link
    
    t = Timer + zSeconds    'timeout value
    
    Application.StatusBar = "Waiting for IE's ready state..."
    'Wait for "IE is ready" state (4=READYSTATE_COMPLETE)
    While .readyState <> 4 And Timer < t: DoEvents: Wend
    
    'check timer and use Document property to check if loaded..
    If Timer < t Then
      Application.StatusBar = "Waiting for hyperlink site..."
      While .document Is Nothing And Timer < t: DoEvents: Wend
    End If
    
    If .document Is Nothing Then
      'Timeout has occurred..
    zTimedOut = True
    End If
    
    ' Activate IE
    Application.StatusBar = False   'clear and reset bottom statusbar
    If zTimedOut Then
    .Quit                   'close Internet Explorer
    Else                    'otherwise..
    .Visible = True         'show hyperlink site
    End If                  'end of timeout test
    
    End With                'end of shortcut
    
    Set ie = Nothing        'tidy up
    On Error GoTo 0         'reset error trapping
    
    End Sub
    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Doesn't that Do loop just keep trying and trying and trying? i.e. it won't timeout?
    Just asking.

    zeddy

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Maud,
    As Z says, won't that just stay in the code until the browser has finished - which it doesn't ?
    PS And is that the same as the code below (I just find it a bit more readable)
    Code:
    If WebBrowser1.ReadyState <> WebBrowserReadyState.Complete Then
    I would use CTRL Break except that my shiny new laptop doesn't have a Break key ! Apparently it might be some other combination such as Fn F6, CTRL ALT ESC, or CTRL Fn Insert - I will have to write a Loop and experiment I think.

    Zeddy,
    Thankz for the code example. I am working on it to underztand how it workz and to adapt it for my caze.
    Looking hopeful.

    Martin

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    There are two issues to deal with
    1. internet connexion problems e.g poorly connected wifi, dropouts, no internet etc.
    2. web site issues

    As far as I know, for new laptops without the [Break] key, just use the [Esc] key.

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Z,
    I think you and Maud have given me enough clues to work up a solution.
    It will take me a while to work out the bugs but I can see my way now.
    Cheers.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..don't stay up all night working on it. Get some zzzzzz's

    zeddy

    (P.Z. I did notice and appreciate your letter zubztitionz!)

  9. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2015-05-06)

Posting Permissions

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