Thanks all.It's a great site to help us with Excel.
I am trying to do an application with Excel 2007 against sql server 2014.
Need to show information from a stored procedure on Excel with vba.
But when run the method execute of the adodb.command object with a stored procedure, the code compile, the sintaxis is correct, but execute return NOTHING.
The recordset do not open because after the execute method have CERO FIELDS.
How can it be?
When run the stored procedure on sql server shows 1298 rows.
The error on the vba code appears when try to call the bof or eof methods of the recordset, means the sintaxis of my code is correct...BUT NOT WORKING
Can please help me, i can't move one step forward without open the recordset.
Blessings
Leonardo
'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
sCnStr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Merchant;Integrated Security=SSPI;Trusted_Connection=Yes;"
oCn.Open sCnStr
If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If
End Function
Sub Test()
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
'Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "RepTotalesRemVenMon"
objCmd.CommandType = adCmdStoredProc
' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
' Automatically fill in parameter info from stored procedure.
'objCmd.Parameters.Refresh
' Set the param value.
'objCmd(1) = "ALFKI"
' Execute once and display...
Set objRs = objCmd.Execute
' objRs.Open objCmd.Execute
'Debug.Print objParm1.Value
'Do While Not objRs.EOF
' Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
' objRs(2) & vbTab & objRs(3)
' objRs.MoveNext
'Loop
' ...then set new param value, re-execute command, and display.
'objCmd(1) = "CACTU"
'Set objRs = objCmd.Execute
'Debug.Print objParm1.Value
'Do While Not objRs.EOF
' Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
' objRs(2) & vbTab & objRs(3)
' objRs.MoveNext
'Loop
'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
'Set objParm1 = Nothing
Exit Sub
ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
'Set objParm1 = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndAutoParamCmd
End Sub
USE [Merchant]
GO
/****** Object: StoredProcedure [dbo].[REPORTE TOTALES DE REMISIONES POR VENDEDOR Y MONEDA] Script Date: 05/23/15 11:14:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[RepTotalesRemVenMon]
AS
BEGIN
DECLARE @Remision real,@Cliente int,@ClieNomComercial varchar(90),@Clienomfiscal varchar(90),@VenNombre varchar(40),@Remtotal decimal(18,2),@Moneda char(1),@RemFecha datetime
DECLARE @Dolartotal decimal(18,2),@Pesostotal decimal(18,2),@Buffervendedor varchar(40)
CREATE TABLE #RepTotalesRemVenMon (
Remision real,
Cliente int,
ClieNomComercial varchar(90),
Clienomfiscal varchar(90),
VenNombre varchar(40),
Remtotal decimal(18,2),
Moneda char(1),
RemFecha datetime,
Vendedor varchar(40),
Dolar char(1),
TotalDolar decimal(18,2),
Pesos char(1),
TotalPesos decimal(18,2)
);
INSERT INTO #RepTotalesRemVenMon(Remision,Cliente,ClieNomComercial,Clienomfiscal,VenNombre,Remtotal,Moneda,RemFecha)
select Remision,fr.cliente,gc.ClieNomComercial, gc.clienomfiscal, fv.VenNombre,remtotal,fr.moneda,RemFecha
from FurRemision fr
join GenCliente gc on Fr.Cliente = gc.Cliente
join FurVendedor fv on fr.Vendedor = fv.Vendedor
where RemFecCancela is not null
order by fv.VenNombre, RemFecha
DECLARE CALCULO_TOT_VEND_MON CURSOR FOR select distinct VenNombre from #RepTotalesRemVenMon
OPEN CALCULO_TOT_VEND_MON
FETCH NEXT FROM CALCULO_TOT_VEND_MON INTO @VenNombre
WHILE @@FETCH_STATUS = 0
BEGIN
select @Remision = max(Remision) from #RepTotalesRemVenMon where VenNombre = @VenNombre
select @Dolartotal = sum(Remtotal) from #RepTotalesRemVenMon where VenNombre = @VenNombre and Moneda = 'D'
select @Pesostotal = sum(Remtotal) from #RepTotalesRemVenMon where VenNombre = @VenNombre and Moneda = 'P'
UPDATE #RepTotalesRemVenMon
SET Vendedor = @VenNombre, Dolar = 'D', TotalDolar = @Dolartotal, Pesos = 'P', TotalPesos = @Pesostotal
WHERE Remision = @Remision
FETCH NEXT FROM CALCULO_TOT_VEND_MON INTO @VenNombre
END
CLOSE CALCULO_TOT_VEND_MON
DEALLOCATE CALCULO_TOT_VEND_MON
select * from #RepTotalesRemVenMon
END
