新聞財經娛樂♬資訊 News Economy

Thanks, Qlemo, your reply trigger me to think more forwards,

The code is completely working, if the number of time or times to refresh is too long and still failed I can ue
ontime to check as follows. Hope this thread help others who got similar issue

Sub CheckQueryRefreshState(qtbl As QueryTable)

      If qtbl.Refreshing Then
         'If the query is still refreshing, call the
         'CheckQueryRefreshState again in one second.
         Application.OnTime Now() + TimeValue("00:00:01"), _
         "CheckQueryRefreshState"
      Else
         'Display the number of rows returned to the query table
         'after the query table is refreshed.
         MsgBox qtbl.ResultRange.Rows.Count & " Rows Returned"
      End If

   End Sub

The issue is came from .refresh from VBA or windows is too short, so sometimes it is work sometimes not .
If we are not able to change register for timeout of refresh or (readystate for navigate web). We can
force the query table to re-do once refresh is fail until is refresh. I think re-do a few time, at least once
is working

1. You are missing the goto label Redo
2. It would be much better to check the Err instead of the cell contents to find out there has been an error
3. Either you miss a On Error Goto 0 after the refresh, or should move the On Error Resume Next out of the loop.

It is solved finally and completely with the following the code by error resume next and check whether refresh is done if the cell is empty or not
for destination range. Now it is working fine ,and without the code improvement add , it is still unstable. for double confirmation
 So I believe the following code  is working
i = 0
Do Until i = 8
Dim sconn As String
sconn = "URL; http://finance.yahoo.com/q/hp?s=%5EDJI&d=11&e=12&f=2010&g=d&a=9&b=1&c=1928&z=66&y=" & i * 66
Debug.Print sconn
With ActiveSheet.QueryTables.Add(Connection:=sconn, Destination:=Range("a" & (1 + i * 67)))


   ' .Name = "Quote: " & symb
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "9"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
   On Error Resume Next
' Sleep (10000)
  .Refresh BackgroundQuery:=False
  Range("A" & 16 + i * 67).Select
  If Range("A" & 16 + i * 67) = "" Then  'if the cell is empty, it imeans, fail refresh
  GoTo Redo
  End If
End With
i = i + 1
Redo:
Err.Clear
Loop

So you want to show how to fight with such trouble - understand that.
In that case you should implement an error handler around the Refresh call, e.g. by not incrementing the counter for the result range. The next loop will then try to read the same page again.

That is for Web query tutorial, and that is good example to solve issue from Webquery from website instead of download csv file