XL2000: Query Table’s Refresh method called with an Excel formula causes the formula to be recalled (causing Refreshing problem to the QueryTable).


SYMPTOMS:

- Our formula is recalled unexpectedly (Kind of Recursivity)
- QueryTables contaning Criterias based on Referenced Cells are not updated as Query table containing normal cells Criteria

DETAILS:

Our program use QueryTable to dynamically update data on Worksheet from an External Database.

We create QueryTable object programmatically to be updated by an ADODB Recordset for an Oracle Database.

We built our ADO Recordset Sql String from Data in a worksheet as Criteria

To dynamically update our QueryTable object with good Criteria we use a Formula that Contains a Reference to the Criteria Range.

Ex:

=ApplyFormula($D$1:$E$1 [c1],"Products_List" [c2],"AutoQryTable_1" [c3],TRUE)

When Criteria in range D1 :E1 are updated, the ApplyFormula is called automatically and Refresh Recordset for the QueryTable in parameter.

To Create the Query Table we use this function that works well:

Public Sub TransferData(p_strQryTable)
'
' Description : Create the QueryTable to transfer data from VBA to Excel with Automation
' Author : Rod Custeau
' Date : 20 Feb. 2002
' File : 1690
'
On Error GoTo TransferData_Err
Dim Rs As ADODB.Recordset
Dim oQryTable As QueryTable

    Set Rs = New ADODB.Recordset
    Rs.Open rsGR.Source, mdbLabLink, adOpenDynamic, adLockReadOnly, adCmdText

    'Create the QueryTable
    Set oQryTable = ActiveSheet.QueryTables.Add(Rs, Output_C)

    oQryTable.Name = p_strQryTable
    oQryTable.AdjustColumnWidth = False
    oQryTable.FieldNames = False
    oQryTable.EnableRefresh = True
    oQryTable.Refresh

TransferData_Exit:
On Error Resume Next
    Rs.Close
    Set Rs = Nothing
    Set oQryTable = Nothing
Exit Sub

TransferData_Err:
    MsgBox Err.Description
    GoTo TransferData_Exit
End Sub


And To Change Recordset to Refresh the QueryTable we use this function:
Note : This function is called by =ApplyFormula () when a criteria is updated

Public Sub ChangeDataFromQryTable(p_strCurrentQryTable As String)
'
' Description : Refresh the QueryTable Recordset to update Results when the
' Criterias change
' Author : Rod Custeau
' Date : 20 Feb. 2002
' File : 1690
'
' Modification : Change criteria and query is not always performed
' Author : Rod Custeau
' Date : March 19, 2002
' File : 3114
'
On Error GoTo ChangeDataFromQryTable_Err

Dim Rs As ADODB.Recordset

    Set Rs = New ADODB.Recordset
    Rs.Open rsGR.Source, mdbLabLink, adOpenDynamic, adLockReadOnly, adCmdText

    Set ActiveSheet.QueryTables(p_strCurrentQryTable).Reco rdset = Rs
    ActiveSheet.QueryTables(p_strCurrentQryTable).Refr esh[c4]

ChangeDataFromQryTable_Exit:
On Error Resume Next
    Rs.Close
    Set Rs = Nothing
Exit Sub

ChangeDataFromQryTable_Err:
    'MsgBox Err.Description
    GoTo ChangeDataFromQryTable_Exit
End Sub

I also tried to add Parameters to get rid of the = ApplyFormula() to be able to use .RefreshOnChange Method, but I was getting an Error 1004 :No value given for one parameter ….

Any Idea ?


[c1]: Criteria Range
[c2]: Oracle View
[c3]: Query table object Reference
[c4]: Using this Refresh method cause the =ApplyFormula() to be Recalled even if execution not finished yet.

HI,

I have the same problem in my code. Have you solved that problem or not?

Thank you.

Try blocking the actions of additional calls: The idea is to prevent additional instances of the sub from being run if it is currently running. It sounds to me as if the running of the sub changes some values that automatically call the sub when they get changed. I have trouble following your description of the problem; it is pretty terse...

But you do say that when the criteria in range D1:E1 change, ApplyFormula is automatically called. If those criteria are then changed by ApplyFormula, there would ba an additional call to ApplyFormula in response to that change.

Just a guess.

Thank you Brian,

Perhaps this code is a bit difficult to understand. I have the same trouble but will put you the code in order to understand the problem.

In a Excel Formula you put in for example:

                           =lista("select * from table1")

Lista check and prepare de SQL and fit the optional parameters and then it calls the other function "CreaConsulta".

When in CreaConsulta is reached the Refresh method, automatically is called lista function again.

And other thing I have probed the application.enableevents = false but it doesn't work.


'This is my function in Excel Formula
Public Function lista(sqlTXT As String, Optional par1 As Variant, Optional
par2 As Variant) As Variant

    Dim salida, auxSalidaOK

    'This check if the connection is opened or not. If it is closed, the
connection is created.
    If Not conexion_abierta Then
        MyConnect
    End If

    salida = ""

    'The parameters are: Par1: Destination Cell if missing is the cell under
the formula. Par2: True o False if you want headings or not.
    auxSalidaOK = CreaConsulta(sqlTXT, IIf(IsMissing(par1),
Application.Caller.Offset(1, 0), par1), IIf(IsMissing(par2), True, par2))
    salida = IIf(auxSalidaOK, "OK", "FALLO SQL")

    lista = salida
End Function



'This is the function that creates the Query
Private Function CreaConsulta(sql As String, Celda As Range, cabecera As
Boolean) As Boolean
    Dim aux As QueryTable
    Dim inter As Range
    Dim salida, nuevaConsulta As Boolean

    salida = False

    Set RS = conn.Execute(sql) 'This is a global definition

    'Look for other QueryTables in order to know if it is a new one or an
old one.
    If Celda.Worksheet.QueryTables.Count > 0 Then
        For Each aux In Celda.Worksheet.QueryTables()
            'Check the range result if intersect or not with our destination.
            Set inter = Application.Intersect(aux.Destination, Celda)

            If inter Is Nothing Then
                nuevaConsulta = True 'It is a new one
            Else
                nuevaConsulta = False 'It is a old one
                Exit For
            End If
        Next aux
    Else
        nuevaConsulta = True 'If there is no QueryTables, is a new one.
    End If

    If Not nuevaConsulta Then
        With Celda.QueryTable
            .FieldNames = IIf(cabecera, True, False)
            .RowNumbers = False
            .FillAdjacentFormulas = True
            .PreserveFormatting = True
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .AdjustColumnWidth = False
            .PreserveColumnInfo = False
            Set .Recordset = RS
            salida = .Refresh(false)
            If .FetchedRowOverflow Then
                MsgBox "There is a lot of rows in the Query."
            End If
        End With
    Else
        With Celda.Worksheet.QueryTables.Add(RS, Celda)
            .FieldNames = IIf(cabecera, True, False)
            .RowNumbers = False
            .FillAdjacentFormulas = True
            .PreserveFormatting = True
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .AdjustColumnWidth = False
            .PreserveColumnInfo = False
            salida = .Refresh(False)
            If .FetchedRowOverflow Then
                MsgBox "There is a lot of rows in the Query."
            End If
        End With
    End If

    CreaConsulta = salida

End Function


The function use in the Formula has to do only one thing. It has to create a
QueryTable to return all the rows and columns in the recordset.

The parameters in the function lista are:

text with the SQL
optional par1 = Destination cell where the Query Table will be inserted
optional par2 = true or false if you want headings or not in the QueryTable
And then the function lista has to return TRUE or FALSE if the QueryTable is
created ok or not. By this way you get a returned value for the formula in
Excel and get the result of the Query.

The probem is that when you get at the point .Refresh(false), insted of
continuing the running code, Excel begins to execute again lista function.

If you have only one of these formulas in you Excel, it works ok because in
the second pass, the code is runned completely. But imagine you have 3
formulas like these with diferent selects.

The running order will be:

lista (select1) -> run CreaConsulta (select1) -> When it arrives at the
point when the Refresh is called, suddenly goes to the following formula, it
is, lista (select2) and then it goes to CreaConsulta (select2) but without
finishing the code of the first formula, and by this way it goes on and on
till the last formula that is runned completely so i get only 1 result from
all i have in my worksheet.
To solve that I have to excute each formula by editing the formula and hit
Return key for each of them.

It seems to be a bug of Excel or that something is controlling some events.
But I think that functions must be finished before executing other.

have you any ideas?