Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have made an Add-in for Excel that creates a QueryTable in a range. It is
a function that returns true o false if the query has been made ok or not.

To make this addin works it is necessary that you write a formula in an
excel cell, this formula call another function that creates a Query table in
a range expecified.

The code works ok but there is a problem.

Firstly I create a connection to the database, then assign the Recordset to
de QueryTable and then I call the refresh method in order to see the results.

When the refresh is called, the code stops running and begin to recalculate
the formula from where i called de query table.

For example:

=GetData("select * from table1")

This function returns true or false in that cell, but also create a
QueryTable under the cell I wrote the formula.
When QueryTable.Refresh is executed, the code suddenly begin to execute
"GetData" again without finishing the other code, so I get an error.
If I have only one of this formulas in my worksheet, it runs ok because in
the second pass the code is runned entirely, but if i have more formulas the
secuence is the following:

Ex: Formula1, Formula2, Formula2

Formula1 -> Is executed until the Refresh method and then pass to the
formula2 without finishing the code of formula1

Formula2 -> Is executed until the Refresh method and then pass to the
formula3 without finishing the code of formula3

Formula3 -> Finish completely the code and returns ok

Has anyone any idea of what is happening?

Thank you in advance.
 
Javier,
As ALWAYS, post your code for comments but you probably need to incorporate
application.enableevents=false
code
=true
 
Javier Ortega said:
Hi,

I have made an Add-in for Excel that creates a QueryTable in a range. It is
a function that returns true o false if the query has been made ok or not.

To make this addin works it is necessary that you write a formula in an
excel cell, this formula call another function that creates a Query table in
a range expecified.

The code works ok but there is a problem.

Firstly I create a connection to the database, then assign the Recordset to
de QueryTable and then I call the refresh method in order to see the results.

When the refresh is called, the code stops running and begin to recalculate
the formula from where i called de query table.

For example:

=GetData("select * from table1")

This function returns true or false in that cell, but also create a
QueryTable under the cell I wrote the formula.
When QueryTable.Refresh is executed, the code suddenly begin to execute
"GetData" again without finishing the other code, so I get an error.
If I have only one of this formulas in my worksheet, it runs ok because in
the second pass the code is runned entirely, but if i have more formulas the
secuence is the following:

Ex: Formula1, Formula2, Formula2

Formula1 -> Is executed until the Refresh method and then pass to the
formula2 without finishing the code of formula1

Formula2 -> Is executed until the Refresh method and then pass to the
formula3 without finishing the code of formula3

Formula3 -> Finish completely the code and returns ok

Has anyone any idea of what is happening?

Thank you in advance.
Click to expand...

Not without seeing any code. I think it would be good if you explain what
kind of problem you are trying to solve.

/Fredrik
 
I'm sorry about the code. here you are:

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, automatically is called lista
function again.

I found a person that have the same problem, you can see the following link:

http://p2p.wrox.com/topic.asp?TOPIC_ID=669


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

Any ideas?
Thank you.

'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
 
Javier Ortega said:
I'm sorry about the code. here you are:

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, automatically is called lista
function again.

I found a person that have the same problem, you can see the following link:

http://p2p.wrox.com/topic.asp?TOPIC_ID=669


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

Any ideas?
Thank you.
Click to expand...

I still don't uderstand the logic. I really think you need to tell what you
want to solve. To me, it only makes sense to have a function with an SQL
statement that does fetch exacly one value and returns that retrieved value.
In this case, it seems as if you get multiple records and I have no idea
where that recordset goes.

What optional parameters are there. The statement lokks perfectly valid to
me except for the fact that it may return many rows and many columns.

You have to show the entire code AND an explanation of what you want to do.
I am sure someone from this group can come up with a descent implementation.
I will have a look at the link you submitted.

/Fredrik
 
Hi Fredrik,

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.

Thank you.
 
Javier Ortega said:
Hi Fredrik,

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.

Thank you.
Click to expand...

This looks very complex. I think your whole idea is flawed. Is there any
sample sheets around with logic similar to yours. The problem described in
the link wasn't very useful. I assume ApplyFormula was a user defined
function but there was no code which made it pretty incomprehensible. I
failed to understand the formula =ApplyFormula($D$1:$E$1
[c1],"Products_List" [c2],"AutoQryTable_1" [c3],TRUE)

What does [c2] mean? What tabout the other parameters? And where is the code
for Applyformula?

Anyway, as far as i can understand, you want to put the result of your query
into some range. That's not something that you should do from a cell
function. I think you should crate a Sub that does this. By doing so, you
will only insert constants which won't cause any kind of recalculation
problem. I still miss a verbal explanation of what you want to accomplish.

/Fredrik
 
Hi Fredrik,

We need to create a QueryTable because there are other formulas that are
referenced to the data in the Query. That is the reason we need to make a
QueryTable instead of copying the values.

We put in a excel formula a user defined function (function 1), this
function calls another one to create a Query Table (function 2). The correct
order is the following:

1st -> lista function is called and executed. It calls CreaConsulta funtion.
2nd -> CreaConsulta makes a QueryTable, if it is created ok, it returns TRUE
if not it returns FALSE.
3rd -> The code returns to the function lista in order to end the rest of
the code that is to return de value returned by CreaConsulta in the value
property of the cell in excel.

For example: You put in A1 the following lista("select * from table1")
lista calls CreaConsulta and creates the QueryTable, if its created A1.value
will be TRUE otherwise will be FALSE. And if the QueryTable has been created,
it will be in B1 cell.

The problem is only that when you get to the refresh method in CreaConsulta,
insted of continuing the code from that point, Excel interrups the executing
code in order to begin executing again the function lista from the beginning
and that reaction makes that the code is not executing correctly because
there is always part of the code without being runned.
 
Back
Top