Store Procedure thru Excel VBA

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
294
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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,790
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
294
Run-time error '-2147217900 (80040e14)':

Procedure SP_GetOrderForCustomer has no parameter and arguments were supplied.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,116
Messages
5,835,476
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top