Query.Refresh in For-Next loop hangs

[DG_Montana]

I've written a routine to populate an excel spreadsheet with a list of geographic regions (cities, counties, congressional districts, etc) that are stored on a server. Each type of geographic region is stored on a separate sheet in the Excel workbook. The lists are then used to populate a form for selection purposes, so performance requires the lists to be stored locally.

Some of the lists (all cities in the US, for example) are very large (35,000+ records) and must be retrieved in 1000-record chunks, again for performance reasons. In Excel 2007/2010, if I disconnect my network connection during the geography refresh for a large data set that is broken into chunks, Excel hangs forever (we've allowed over 30 minutes in our testing) and must be closed and restarted. In Excel 2007/2010 if I disconnect my network connection during the geography refresh for a small data set (such as congressional districts) that isn't broken into chunks, Excel reports the disconnect error and gracefully terminates the process according to the error handling I have in place. Oddly, in Excel 2003, Excel terminates the process gracefully whether I'm refreshing a large, "chunked" data set or a small one. Even more interestingly, if I reconnect after 5 or 10 minutes, Excel 2007/2010 picks right back up where it left off and continues with the fetching of the data.

As you can see from the code sample below, exactly the same procedure is called for both small and large data sets:

If Iterate = False Then ' this is used for small data sets
    
        ' Run the query once
        If Not GeogLists.QueryBuild() Then
            Err.Raise glHANDLED_ERROR
        End If
        
    Else ' this is used for large, "chunked" data sets
                        
        ' For each 1000 rows in the data set, run the query once
        For i = 0 To NumRefresh - 1
                            
            DestRow = (i * 1000)
            
            GeogRange = "$A$" & (DestRow + 1)
            StartRow = DestRow
            
            ' Run the query multiple times

            If Not GeogLists.QueryBuild() Then 
                Err.Raise glHANDLED_ERROR
            End If

        Next i
        
    End If

The function that is called appears below, edited for anonymity. If someone could suggest a method for forcing Excel 2007/2010 to stop trying to refresh and run my error handling code when the network connection is unavailable, I'd REALLY appreciate it!!! The DoWhile/DoEvents loop was my attempt at forcing that, but it hasn't had any effect. Thanks!!!

Public Function QueryBuild() As Boolean

    Dim URL As String ' the web address of the query results
    Dim lAttempt As Long ' the number of connection attempts made

    Const sSOURCE As String = "QueryBuild"
    Dim bReturn As Boolean
    Dim qt As QueryTable

    On Error GoTo ErrorHandler
    
    bReturn = True ' Assume the procedure succeeded until an error is encountered
    
    ' Update Excel's Status Bar
    
    Application.StatusBar = "Refreshing " & QryName & " " & StartRow

    ' Build the Query Connection String
               
    URL = "URL;http://www.urltogetdata.aspx?&startrow=" & StartRow & "&query=" & QryName

    Set qt = GeogWB.Worksheets(GeogWS).QueryTables.Add(Connection:=URL, _
        Destination:=Range(GeogRange))
    
    With qt
        .Name = QryName
        .RefreshStyle = xlOverwriteCells
        On Error GoTo RetryConnection
        .Refresh BackgroundQuery:=False
        Do While qt.Refreshing
            DoEvents
        Loop
        On Error GoTo ErrorHandler
    End With

ErrorExit:

    ' Update Excel's Status Bar
    
    Application.StatusBar = False
        
    ' If there was an error during the building of the connection, remove it
    If bReturn = False Then qt.Delete
    
    QueryBuild = bReturn
    Exit Function
    
RetryConnection:

    ' Attempt to make the connection three times before bailing out.
    
    If lAttempt < 2 Then
        Application.StatusBar = "Retrying connection..."
        lAttempt = lAttempt + 1
        Application.Wait Now + TimeSerial(0, 0, 3) ' wait 3 seconds before retrying connection
        Resume
    End If
        
    Err.Description = "Nice error message in user-friendly English" & _
        Chr(10) & Chr(10) & Err.Description

ErrorHandler:
    
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

End Function

 

[DG_Montana]

I'm a bit surprised that no one has replied to this thread since I posted it on Feb 16th. Is there something wrong with my post?

 

[DG_Montana]

After reading a bit about this forum, I realize that my question is probably a bit more complex than most. In the past I've received an answer within hours or days, but I understand members' reluctance to dive into this one. Perhaps someone can direct me to a Microsoft website where I might report this as a possible problem with Excel 2007/2010? As I mentioned, it's working well in Excel 2003. A thousand thanks to any brave soul willing to dive into this one!!

 

[Firefly2012]

I agree this is more complex than the majority of the questions on the board

Also, except for simple questions, being able to recreate the circumstances and environment within which the question is phrased is usually beneficial. This isn't really possible in your particular circumstance.

The only thing that I took particular note of is that you have an unqualified reference in the querytable.add:

Set qt = GeogWB.Worksheets(GeogWS).QueryTables.Add(Connection:=URL, _
Destination:=GeogWB.Worksheets(GeogWS).Range(GeogRange))

I have highlighted the amendment (you could of course have used a With ... End With).

I would think if this was going to cause a problem it would do so on any version of Excel though, so I assume you are activating the relevant sheet prior to running this QueryTable add function (and the code isn't contained within a sheet module)?

 

[Firefly2012]

The other thing is have you stepped thru the code using F8 to see what happens to program execution?

Does the code always time out in 2007/2010 or only some of the time?

 

[DG_Montana]

Thanks for the reply! You're definitely a brave soul to wade into this one!!

I didn't spot the unqualified reference, so thanks for pointing that out. I'll make the change, see if it helps and report back.

Infuriatingly, if I step through the code using F8, or even if I'm just running the code in debug mode by setting an earlier break point but letting it cruise through the query.refresh with F5, it works fine in all versions of Excel.

It ALWAYS times out in Excel 2007/2010, not just some of the time.

I appreciate your comments!

 

[Firefly2012]

Infuriatingly, if I step through the code using F8, or even if I'm just running the code in debug mode by setting an earlier break point but letting it cruise through the query.refresh with F5, it works fine in all versions of Excel.

It ALWAYS times out in Excel 2007/2010, not just some of the time.

Sounds like it might be some form of timing issue then (maybe). Not easy to debug it if it always works when you step thru