Store Procedure thru Excel VBA

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
341
Office Version
  1. 365
Platform
  1. Windows
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
What's the error?
 
Upvote 0
Run-time error '-2147217900 (80040e14)':

Procedure SP_GetOrderForCustomer has no parameter and arguments were supplied.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top