EXCEL Issue retriving data from SQL

[Alfredo Hernandez]

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.
http://www.vycmex.com.mx/temporales/QueryAnalizer_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 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)
http://www.vycmex.com.mx/temporales/excel_multiplerow.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.

 

[Patrick Molloy]

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]

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]

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]

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]

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]

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]

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