Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
Hello ppl,
The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
Thanks for the fast reply Patrick,
I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
how does your VBA code look?
(here db is the database connection that is already opened.) Set rst = New Recordset SQL = "sp_LibroVentas '20090501','20090531'" rst.Open SQL, db, adOpenStatic, adLockOptimistic "Alfredo" <Alfredo @discussions.microsoft.com wrote in message ... Thanks for the fast reply Patrick, I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
Here is the call, to the function that import the data
' Establecer el query o stored procedure a ejecutar strSQL = "EXECUTE sp_LibroVentas '" & Format(dteFechaInicial, "MM-DD-YYYY") & "', '" & Format(dteFechaFinal, "MM-DD-YYYY") & "'" ' Importar la información a la hoja lngRegistros = dfImportarRegistros(gconBD, "VXMETRO", strSQL, Hoja2, lngRegistros + 1, True, bolBandera) Where, - gconBD, is the database connection objet - VXMETRO is the data base where the SP will be executed - strSQL is the previusly set query or stored procedure - Hoja2 is the WorkSheet where the resultset will be copied - lngRegistros + 1, is the row where we are going to start to cpy the results - True, is a bolean value indicating if the WorkSheet will be cleaned before, the copy - bolBandera, is a bolean value indicating if the COL Headers will be show or not And here is the version of the code that uses .CopyFromRecordset (The comments are in spanish): Private Function dfImportarRegistros(ByRef conConexion As ADODB.Connection, ByVal strSucursal As String, ByVal strSQL As String, ByRef varHoja As Variant, _ ByVal lngRenglonInicial As Long, ByVal bolLimpiarHoja As Boolean, Optional bolEncabezados As Boolean = True) As Integer ' Declaración de variables Dim lngColumna As Long Dim lngRenglon As Long Dim rstResultados As ADODB.Recordset ' Asignar el valor falso a la funcion en caso de un error dfImportarRegistros = -1 ' Activar la captura de errorres On Error GoTo RutinaError ' Cerrar la conexión If Not conConexion Is Nothing Then conConexion.Close Set conConexion = Nothing End If ' Inicializar el objeto de conexión Set conConexion = New ADODB.Connection ' Establecer los valores de la conexion y abrirla conConexion.CommandTimeout = 0 conConexion.Open "Provider=sqloledb;Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;" & _ "Initial Catalog=" & strSucursal & ";User ID=XXX;Password=XXX;" ' Inicializar el recordset Set rstResultados = New ADODB.Recordset rstResultados.Open strSQL, conConexion, adOpenStatic ' Evitar que la pantalla parpade cuando se actualiza la información Application.ScreenUpdating = False ' Limipiar la el contenido de la hoja destino If bolLimpiarHoja Then varHoja.Activate varHoja.Cells.Select Selection.ClearContents End If ' Si se encontraron resultados, vaciar en la hoja destino If Not rstResultados.EOF And Not rstResultados.BOF Then ' Si se activa la opción de encabezados mostrarlos u omitirlos If bolEncabezados Then ' Establecer el titulo de las columnas For lngColumna = 0 To rstResultados.Fields.Count - 1 varHoja.Cells(1, lngColumna + 1) = rstResultados.Fields(lngColumna).Name Next ' Formatear el encabezado varHoja.Range(Cells(1, 1), Cells(1, lngColumna)).Select dfFormateaEncabezado True End If ' Establecer el renglon inicial en el que se iniciara el volcado de información lngRenglon = lngRenglonInicial ' Volcar el contenido del query en las celdas varHoja.Range("A" & CStr(lngRenglon)).CopyFromRecordset rstResultados ' Obtener el regnglon final del volcado lngRenglon = lngRenglon + rstResultados.RecordCount ' Ajustar el tamaño de las celdas Cells.Select Cells.EntireColumn.AutoFit varHoja.Range("A1:A1").Select Else ' Salir de la función Exit Function End If ' Cerrar los objetos de conexion rstResultados.Close Set rstResultados = Nothing conConexion.Close Set conConexion = Nothing ' Asignar el valor de exito a la función dfImportarRegistros = lngRenglon - 1 ' Desactivar el control de errores y salir de la función On Error GoTo 0 Exit Function RutinaError: ' Desactivar el control de errores On Error GoTo 0 End Function "Patrick Molloy" wrote: how does your VBA code look? (here db is the database connection that is already opened.) Set rst = New Recordset SQL = "sp_LibroVentas '20090501','20090531'" rst.Open SQL, db, adOpenStatic, adLockOptimistic "Alfredo" <Alfredo @discussions.microsoft.com wrote in message ... Thanks for the fast reply Patrick, I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
try this
strSQL = "EXECUTE sp_LibroVentas '" & Format$(dteFechaInicial, "YYYYMMDD") & "', '" & Format$(dteFechaFinal, "YYYYMMDD") & "'" also, to make it easier to read, IMHO strSQL = "EXECUTE sp_LibroVentas 'firstDate', 'secondDate'" strSQL = REPLACE(strSQL ,"firstDate",Format$(dteFechaInicial, "YYYYMMDD")) strSQL = REPLACE(strSQL ,"secondDate",Format$(dteFechaFinal, "YYYYMMDD")) "Alfredo" wrote in message ... Here is the call, to the function that import the data ' Establecer el query o stored procedure a ejecutar strSQL = "EXECUTE sp_LibroVentas '" & Format(dteFechaInicial, "MM-DD-YYYY") & "', '" & Format(dteFechaFinal, "MM-DD-YYYY") & "'" ' Importar la información a la hoja lngRegistros = dfImportarRegistros(gconBD, "VXMETRO", strSQL, Hoja2, lngRegistros + 1, True, bolBandera) Where, - gconBD, is the database connection objet - VXMETRO is the data base where the SP will be executed - strSQL is the previusly set query or stored procedure - Hoja2 is the WorkSheet where the resultset will be copied - lngRegistros + 1, is the row where we are going to start to cpy the results - True, is a bolean value indicating if the WorkSheet will be cleaned before, the copy - bolBandera, is a bolean value indicating if the COL Headers will be show or not And here is the version of the code that uses .CopyFromRecordset (The comments are in spanish): Private Function dfImportarRegistros(ByRef conConexion As ADODB.Connection, ByVal strSucursal As String, ByVal strSQL As String, ByRef varHoja As Variant, _ ByVal lngRenglonInicial As Long, ByVal bolLimpiarHoja As Boolean, Optional bolEncabezados As Boolean = True) As Integer ' Declaración de variables Dim lngColumna As Long Dim lngRenglon As Long Dim rstResultados As ADODB.Recordset ' Asignar el valor falso a la funcion en caso de un error dfImportarRegistros = -1 ' Activar la captura de errorres On Error GoTo RutinaError ' Cerrar la conexión If Not conConexion Is Nothing Then conConexion.Close Set conConexion = Nothing End If ' Inicializar el objeto de conexión Set conConexion = New ADODB.Connection ' Establecer los valores de la conexion y abrirla conConexion.CommandTimeout = 0 conConexion.Open "Provider=sqloledb;Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;" & _ "Initial Catalog=" & strSucursal & ";User ID=XXX;Password=XXX;" ' Inicializar el recordset Set rstResultados = New ADODB.Recordset rstResultados.Open strSQL, conConexion, adOpenStatic ' Evitar que la pantalla parpade cuando se actualiza la información Application.ScreenUpdating = False ' Limipiar la el contenido de la hoja destino If bolLimpiarHoja Then varHoja.Activate varHoja.Cells.Select Selection.ClearContents End If ' Si se encontraron resultados, vaciar en la hoja destino If Not rstResultados.EOF And Not rstResultados.BOF Then ' Si se activa la opción de encabezados mostrarlos u omitirlos If bolEncabezados Then ' Establecer el titulo de las columnas For lngColumna = 0 To rstResultados.Fields.Count - 1 varHoja.Cells(1, lngColumna + 1) = rstResultados.Fields(lngColumna).Name Next ' Formatear el encabezado varHoja.Range(Cells(1, 1), Cells(1, lngColumna)).Select dfFormateaEncabezado True End If ' Establecer el renglon inicial en el que se iniciara el volcado de información lngRenglon = lngRenglonInicial ' Volcar el contenido del query en las celdas varHoja.Range("A" & CStr(lngRenglon)).CopyFromRecordset rstResultados ' Obtener el regnglon final del volcado lngRenglon = lngRenglon + rstResultados.RecordCount ' Ajustar el tamaño de las celdas Cells.Select Cells.EntireColumn.AutoFit varHoja.Range("A1:A1").Select Else ' Salir de la función Exit Function End If ' Cerrar los objetos de conexion rstResultados.Close Set rstResultados = Nothing conConexion.Close Set conConexion = Nothing ' Asignar el valor de exito a la función dfImportarRegistros = lngRenglon - 1 ' Desactivar el control de errores y salir de la función On Error GoTo 0 Exit Function RutinaError: ' Desactivar el control de errores On Error GoTo 0 End Function "Patrick Molloy" wrote: how does your VBA code look? (here db is the database connection that is already opened.) Set rst = New Recordset SQL = "sp_LibroVentas '20090501','20090531'" rst.Open SQL, db, adOpenStatic, adLockOptimistic "Alfredo" <Alfredo @discussions.microsoft.com wrote in message ... Thanks for the fast reply Patrick, I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
I have made the changes you suggest, with no succes.
I got the same results, one row in analyzer, and two in EXCEL. "Patrick Molloy" wrote: try this strSQL = "EXECUTE sp_LibroVentas '" & Format$(dteFechaInicial, "YYYYMMDD") & "', '" & Format$(dteFechaFinal, "YYYYMMDD") & "'" also, to make it easier to read, IMHO strSQL = "EXECUTE sp_LibroVentas 'firstDate', 'secondDate'" strSQL = REPLACE(strSQL ,"firstDate",Format$(dteFechaInicial, "YYYYMMDD")) strSQL = REPLACE(strSQL ,"secondDate",Format$(dteFechaFinal, "YYYYMMDD")) "Alfredo" wrote in message ... Here is the call, to the function that import the data ' Establecer el query o stored procedure a ejecutar strSQL = "EXECUTE sp_LibroVentas '" & Format(dteFechaInicial, "MM-DD-YYYY") & "', '" & Format(dteFechaFinal, "MM-DD-YYYY") & "'" ' Importar la información a la hoja lngRegistros = dfImportarRegistros(gconBD, "VXMETRO", strSQL, Hoja2, lngRegistros + 1, True, bolBandera) Where, - gconBD, is the database connection objet - VXMETRO is the data base where the SP will be executed - strSQL is the previusly set query or stored procedure - Hoja2 is the WorkSheet where the resultset will be copied - lngRegistros + 1, is the row where we are going to start to cpy the results - True, is a bolean value indicating if the WorkSheet will be cleaned before, the copy - bolBandera, is a bolean value indicating if the COL Headers will be show or not And here is the version of the code that uses .CopyFromRecordset (The comments are in spanish): Private Function dfImportarRegistros(ByRef conConexion As ADODB.Connection, ByVal strSucursal As String, ByVal strSQL As String, ByRef varHoja As Variant, _ ByVal lngRenglonInicial As Long, ByVal bolLimpiarHoja As Boolean, Optional bolEncabezados As Boolean = True) As Integer ' Declaración de variables Dim lngColumna As Long Dim lngRenglon As Long Dim rstResultados As ADODB.Recordset ' Asignar el valor falso a la funcion en caso de un error dfImportarRegistros = -1 ' Activar la captura de errorres On Error GoTo RutinaError ' Cerrar la conexión If Not conConexion Is Nothing Then conConexion.Close Set conConexion = Nothing End If ' Inicializar el objeto de conexión Set conConexion = New ADODB.Connection ' Establecer los valores de la conexion y abrirla conConexion.CommandTimeout = 0 conConexion.Open "Provider=sqloledb;Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;" & _ "Initial Catalog=" & strSucursal & ";User ID=XXX;Password=XXX;" ' Inicializar el recordset Set rstResultados = New ADODB.Recordset rstResultados.Open strSQL, conConexion, adOpenStatic ' Evitar que la pantalla parpade cuando se actualiza la información Application.ScreenUpdating = False ' Limipiar la el contenido de la hoja destino If bolLimpiarHoja Then varHoja.Activate varHoja.Cells.Select Selection.ClearContents End If ' Si se encontraron resultados, vaciar en la hoja destino If Not rstResultados.EOF And Not rstResultados.BOF Then ' Si se activa la opción de encabezados mostrarlos u omitirlos If bolEncabezados Then ' Establecer el titulo de las columnas For lngColumna = 0 To rstResultados.Fields.Count - 1 varHoja.Cells(1, lngColumna + 1) = rstResultados.Fields(lngColumna).Name Next ' Formatear el encabezado varHoja.Range(Cells(1, 1), Cells(1, lngColumna)).Select dfFormateaEncabezado True End If ' Establecer el renglon inicial en el que se iniciara el volcado de información lngRenglon = lngRenglonInicial ' Volcar el contenido del query en las celdas varHoja.Range("A" & CStr(lngRenglon)).CopyFromRecordset rstResultados ' Obtener el regnglon final del volcado lngRenglon = lngRenglon + rstResultados.RecordCount ' Ajustar el tamaño de las celdas Cells.Select Cells.EntireColumn.AutoFit varHoja.Range("A1:A1").Select Else ' Salir de la función Exit Function End If ' Cerrar los objetos de conexion rstResultados.Close Set rstResultados = Nothing conConexion.Close Set conConexion = Nothing ' Asignar el valor de exito a la función dfImportarRegistros = lngRenglon - 1 ' Desactivar el control de errores y salir de la función On Error GoTo 0 Exit Function RutinaError: ' Desactivar el control de errores On Error GoTo 0 End Function "Patrick Molloy" wrote: how does your VBA code look? (here db is the database connection that is already opened.) Set rst = New Recordset SQL = "sp_LibroVentas '20090501','20090531'" rst.Open SQL, db, adOpenStatic, adLockOptimistic "Alfredo" <Alfredo @discussions.microsoft.com wrote in message ... Thanks for the fast reply Patrick, I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Issue retriving data from SQL
ok. we want to see the "raw" data filtered for the single record
So after this line rstResultados.Open strSQL, conConexion, adOpenStatic delete all the following code and put this rstResultados.Filter = "[Clave Client] = '02552' " cells.clearContents Range("A1").CopyFromRecordset rst thats it. the filter will hide all but the one record so that you get one line in the spreadsheet if you get two lines, then that's what the SQL server is giving you "Alfredo" wrote in message ... I have made the changes you suggest, with no succes. I got the same results, one row in analyzer, and two in EXCEL. "Patrick Molloy" wrote: try this strSQL = "EXECUTE sp_LibroVentas '" & Format$(dteFechaInicial, "YYYYMMDD") & "', '" & Format$(dteFechaFinal, "YYYYMMDD") & "'" also, to make it easier to read, IMHO strSQL = "EXECUTE sp_LibroVentas 'firstDate', 'secondDate'" strSQL = REPLACE(strSQL ,"firstDate",Format$(dteFechaInicial, "YYYYMMDD")) strSQL = REPLACE(strSQL ,"secondDate",Format$(dteFechaFinal, "YYYYMMDD")) "Alfredo" wrote in message ... Here is the call, to the function that import the data ' Establecer el query o stored procedure a ejecutar strSQL = "EXECUTE sp_LibroVentas '" & Format(dteFechaInicial, "MM-DD-YYYY") & "', '" & Format(dteFechaFinal, "MM-DD-YYYY") & "'" ' Importar la información a la hoja lngRegistros = dfImportarRegistros(gconBD, "VXMETRO", strSQL, Hoja2, lngRegistros + 1, True, bolBandera) Where, - gconBD, is the database connection objet - VXMETRO is the data base where the SP will be executed - strSQL is the previusly set query or stored procedure - Hoja2 is the WorkSheet where the resultset will be copied - lngRegistros + 1, is the row where we are going to start to cpy the results - True, is a bolean value indicating if the WorkSheet will be cleaned before, the copy - bolBandera, is a bolean value indicating if the COL Headers will be show or not And here is the version of the code that uses .CopyFromRecordset (The comments are in spanish): Private Function dfImportarRegistros(ByRef conConexion As ADODB.Connection, ByVal strSucursal As String, ByVal strSQL As String, ByRef varHoja As Variant, _ ByVal lngRenglonInicial As Long, ByVal bolLimpiarHoja As Boolean, Optional bolEncabezados As Boolean = True) As Integer ' Declaración de variables Dim lngColumna As Long Dim lngRenglon As Long Dim rstResultados As ADODB.Recordset ' Asignar el valor falso a la funcion en caso de un error dfImportarRegistros = -1 ' Activar la captura de errorres On Error GoTo RutinaError ' Cerrar la conexión If Not conConexion Is Nothing Then conConexion.Close Set conConexion = Nothing End If ' Inicializar el objeto de conexión Set conConexion = New ADODB.Connection ' Establecer los valores de la conexion y abrirla conConexion.CommandTimeout = 0 conConexion.Open "Provider=sqloledb;Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;" & _ "Initial Catalog=" & strSucursal & ";User ID=XXX;Password=XXX;" ' Inicializar el recordset Set rstResultados = New ADODB.Recordset rstResultados.Open strSQL, conConexion, adOpenStatic ' Evitar que la pantalla parpade cuando se actualiza la información Application.ScreenUpdating = False ' Limipiar la el contenido de la hoja destino If bolLimpiarHoja Then varHoja.Activate varHoja.Cells.Select Selection.ClearContents End If ' Si se encontraron resultados, vaciar en la hoja destino If Not rstResultados.EOF And Not rstResultados.BOF Then ' Si se activa la opción de encabezados mostrarlos u omitirlos If bolEncabezados Then ' Establecer el titulo de las columnas For lngColumna = 0 To rstResultados.Fields.Count - 1 varHoja.Cells(1, lngColumna + 1) = rstResultados.Fields(lngColumna).Name Next ' Formatear el encabezado varHoja.Range(Cells(1, 1), Cells(1, lngColumna)).Select dfFormateaEncabezado True End If ' Establecer el renglon inicial en el que se iniciara el volcado de información lngRenglon = lngRenglonInicial ' Volcar el contenido del query en las celdas varHoja.Range("A" & CStr(lngRenglon)).CopyFromRecordset rstResultados ' Obtener el regnglon final del volcado lngRenglon = lngRenglon + rstResultados.RecordCount ' Ajustar el tamaño de las celdas Cells.Select Cells.EntireColumn.AutoFit varHoja.Range("A1:A1").Select Else ' Salir de la función Exit Function End If ' Cerrar los objetos de conexion rstResultados.Close Set rstResultados = Nothing conConexion.Close Set conConexion = Nothing ' Asignar el valor de exito a la función dfImportarRegistros = lngRenglon - 1 ' Desactivar el control de errores y salir de la función On Error GoTo 0 Exit Function RutinaError: ' Desactivar el control de errores On Error GoTo 0 End Function "Patrick Molloy" wrote: how does your VBA code look? (here db is the database connection that is already opened.) Set rst = New Recordset SQL = "sp_LibroVentas '20090501','20090531'" rst.Open SQL, db, adOpenStatic, adLockOptimistic "Alfredo" <Alfredo @discussions.microsoft.com wrote in message ... Thanks for the fast reply Patrick, I have already removed the field date from the Store Procedure, and the result is the same. Any other idea? Thanks in advance. "Patrick Molloy" wrote: I like this question . Your English is fine and the two pictures showing the results are really clear. I suspect that the answer is in the dates... for example, is 05-01-2009 being handled correctly? it may be coming into the SQL Server as 1st May or 5th Jan? change your dates to 20090501 / 20090531 "Alfredo Hernandez" <Alfredo wrote in message ... Hello ppl, The first thing of all i apologize myself for my bad English. Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first macro that i write, i have already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it. Ej. I have a query that retrieve information from some tables, it group the results, and make some maths and return a single row per document, i tested this view and works fine when you execute it from Query Analyzer, or the MMC The following image is an example of the data returned by the query in the query analyzer. http://www.vycmex.com.mx/temporales/..._singlerow.JPG As you can see there is a set of records returned, but we will focus on the one higlighted, only one row returned by the SP, also you can notice that Im using the tools from SQL Server 2008, but the server is 2K (this is just a note) Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only one or two records. The following image shows the same record i already show in the last image but in EXCEL the row is ungrouped, why? How? i dont know. also you can notice that if you sum the values of the cols SUBTOTAL, IVA, IMPORTE, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003) http://www.vycmex.com.mx/temporales/...ultiplerow.JPG So what things I have already tried, - I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result from the recordset and copy it in the EXCEL Sheet. I change the way i connect to the database, ODBC, ADO, ADO.NET - I rewrite the query thinking that it was the cause but i already change it two times, and i get the same result. So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue. Thanks in advance and best regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retriving text from a column | Excel Discussion (Misc queries) | |||
retriving specific data????? | Excel Worksheet Functions | |||
Retriving name of cobntrolelement | Excel Programming | |||
Help with retriving data from other closed Workbooks | Excel Programming | |||
Retriving a deleted version of Excel | Excel Worksheet Functions |