Store Procedure thru Excel VBA

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
276
Dear Expert

I have table "'AR1" in SQL Server "XYZ'' in Database ''Model''

and store one store procedure



USE [model]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetOrdersForCustomer]
@Account nchar(5)
AS
BEGIN
SELECT *
FROM AR1
WHERE Account = @Account
END

________________________________________________________________________________________

and i have Excel VBA Code to extract data from SQL table from excel parameter as follow

Code:
Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=NETAFIM;Initial Catalog=MODEL;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con


Dim prmAccount As ADODB.Parameter


' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("Account", adVarChar, adParamInput, 10, Range("D2").Text)


Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "SP_GetOrdersForCustomer"
Set rs = cmd.Execute(, , adCmdStoredProc)


' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs


rs.Close
Set rs = Nothing
Set cmd = Nothing


con.Close
Set con = Nothing


Application.StatusBar = "Data successfully updated."
End Sub

But i am getting Error on below line


Set rs = cmd.Execute(, , adCmdStoredProc)

Please help i am just near about to my project
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What's the error?
 

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
276
Run-time error '-2147217900 (80040e14)':

Procedure SP_GetOrderForCustomer has no parameter and arguments were supplied.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,808
Messages
5,524,995
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top