EXCEL Issue retriving data from SQL

[oderflazone]

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 math’s 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.

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 I’m 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 don’t 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)

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.

 

[Norie]

It might help if you posted a few more things, say the VBA code, perhaps even the SQL for the query.

The first thing that springs to mind when records you expect to group don't group is that there might be some problem with the data.

It could be something as simple as an errant space somewhere.

Itr could be the dates, in the first image you appear to have a date/time field with the time being 00:00:00.000.

In the second image there appears to be no time part, but if there is a time part then that could be what is causing the problem with the grouping.

 

[oderflazone]

Hey Norie thanks for the fast reply,

Well the fact is that the Marco is calling the store procedure exactly as i show in the image of the Query Analizer, so if the query analizer returns only one row, why EXCEL is ungrouping it, if i'm not making any operation with the recordset, only wirting it on the Worksheet.

The store procedure uses query that is very large and include like five tables, and i think that the ppl who read it will need a deatiled explanation on how it works, but the resume is that the SP returns only one row, and when execute the query from EXCEL, returns two.

I think that if exist a problem with the data, the results from the SP will show ungrouped (Like EXCEL) two records instead of one, or what do you think?

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

 

[Norie]

Are you sure Excel is doing the 'ungrouping'?

Also, the results don't seem to be the same - in the first the Fecha field has the date and time, while in the second (Excel) there's only the date.

Is the date/time field in the source data static?

Could the stored procedure possibly be changing it in anyway?

Are you actually interested in the time? If not why not change the query(s) to only return the date.

If you format that field in Excel to something like 'mm/dd/yy 00:00:00' what do you see?

 

[oderflazone]

Well bro, at this point i'm not sure of anything in this scenario, but i have made several test and i think the problem is with the objet recordset that i use in the macro, because i retrieve the other rows correctly, but when i reach that one, the recordset returns two rows.

Ahh the date it's a static field, it change only per document, the diference in the fields of the data base and the EXCEL Worksheet, is the format of the COL in EXCEL the date is showed with the format DD/MM/YYYY, i have already removed that field (DATE) from the SP, and the result is the same.

Could the stored procedure possibly be changing it in anyway?

I really don't know, because for me if the Stored Procedure return that resultset in the query analyzer it must be the same for all the other aplications that call the Stored Procedure, i will write a vb6 aplication that execute the SP and show it in a grid to see what happens.

If you format that field in Excel to something like 'mm/dd/yy 00:00:00' what do you see?

Well i tried this too, and the result is the same.

 

[Norie]

Can you export/import the results to any other application?

I can't see how Excel can be causing this, as far as I'm aware CopyRecordSet does just that, copy the recordset - it's not going to change any values.

 

[oderflazone]

From the query analyzer?

 

[Norie]

Sorry, I don't understand the question.