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://i.sstatic.net/RWNVj.jpg

Picture 2: https://i.sstatic.net/VnEk1.jpg - 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://i.sstatic.net/sM4BR.jpg - 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.

@QHarr Thanks for the link, it appears to be exactly what I need however I am getting an error stating "Query results cannot overlap a table or XML mapping. Please select another destination" any ideas? — Specialized48
– Specialized48, Commented Aug 19, 2018 at 19:42
Not sure at a distance and I can't currently view your images. Have you specified an output destination that overlaps with an existing Excel table or xmlmap? — QHarr
– QHarr, Commented Aug 19, 2018 at 19:46
@QHarr Silly error from me, ignore my previous comment. Just another thing, I have successfully got the data in now thanks to your link, however when I try to place the data in A14 as shown in the original post above it shifts all the current columns to the right when placing the data as shown in the two pictures below: — Specialized48
– Specialized48, Commented Aug 19, 2018 at 19:55
I can't view the images. Sometimes imgur is weird like that and shows black backrounds without images. It sounds vaguely familiar what you have said in terms of I think I have seen someone write about it before. I would check what google has to say. If not answered by tomorrow I will have a look again when I may be able to view your images. Would it be possible to specify a destination to the right of the existing data to see if that avoids the shift? — QHarr
– QHarr, Commented Aug 19, 2018 at 20:01

ASH · Accepted Answer · 2018-08-22 21:40:53Z

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.

answered Aug 22, 2018 at 21:40

ASH

20.5k28 gold badges116 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass parameters to SQL from Excel?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related