Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
I'm sorry about the code. here you a
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 a 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
"Javier Ortega" wrote in message ... I'm sorry about the code. here you a 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. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
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 a 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
"Javier Ortega" wrote in message ... 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 a 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. 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP with QueryTable, ADO, EXCEL and EXCEL'S Formulas
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Web Query using a QueryTable via HTTPS?? | Excel Programming | |||
Excel 2003: Can the same range be used for a querytable and a list | Excel Programming | |||
Excel - VBA - QueryTable error 1004 | Excel Programming | |||
ODBC / OLE DB QueryTable Editor Add-In for Excel | Excel Programming |