sql server - How to pass parameters to SQL from Excel?

Question

I am trying to pass a parameter from Excel to a SQL Server Stored Procedure. The parameter is a date, the aim is to pass the date into the query then the query return results for said date. I have included pictures of how I have currently connected to the database.

Picture 1 (Irrelevant really but thought I'd include it anyway): https://imgur.com/TgRKOkc

Picture 2: https://imgur.com/FkH34qQ - Here I am currently hardcoding the parameter in the .CommandText area to check whether the functionality is working OK if the parameter were to be passed correctly. The data returned is correct with the hard coded value. This is where I am hoping to replace the '2018-08-19' with a dynamic parameter entered into cell A14 of the spreadsheet by the client.

Picture 3: https://imgur.com/3LGTP3I - This is where I feel like I am messing up, I am brand new to VBA so I am unaware how to declare the value entered in a particular cell (A14 in this case) as the parameter to pass to the stored procedure on refresh of the excel document. Worth noting I am aware that I am point to "PreDealingFormA" in the VBA code and the connection is "PreDealingFormA1" this is just an anomaly in the screen shots, I have since changed this and it hasn't solved the problem. I am aware that the code pictured in screenshot three is on the command of a button being clicked, I previously thought this was the route to go down, however due to requirements a button cannot be implemented. The aim is to instead pass the parameter entered into cell A14 and execute the stored procedure on refresh of the excel document.

Any help is appreciated on this as I am brand new to VBA so as basic as this may seem, it's hard for me to get my head around at the moment.

Answer 1

You can do it like this.

Sub RunSProc()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Data Source=Server_Name;"
strConn = strConn & "Initial Catalog=DB_Name;"
strConn = strConn & "Integrated Security=SSPI;"

cn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "MyOrders"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Range("E1").Text
cmd.Parameters(2).Value = ActiveSheet.Range("E2").Text
Set rs = cmd.Execute()

If Not rs.EOF Then
    Worksheets("sheet2").Range("A5:D500").CopyFromRecordset rs
    rs.Close
End If

End Sub

In this case, the setup looks like this.