I have a stored procedure with four parameters, I'm trying to connect it to an Excel 2010 workbook but I'm having difficulty passing multiple parameters.
I followed the steps outlined in Running a SQL Stored Procedure in Excel and was able to connect my stored procedure to Excel and pass one parameter.
But after I got that to work I had to modify my stored procedure and add three more parameters and now I can't figure out the syntax for the command text in my Excel connection to pass multiple parameters.
My stored procedure name is [VIDIR].[_cornie_Report_Shipped_Serial_Numbers]
and I used the following VBA code in my Excel workbook to successfully refresh the connection with only one parameter.
Sub RefreshQuery()
With ActiveWorkbook.Connections("VIDIR3 Arborg_Test_App country").OLEDBConnection
.CommandText = "[VIDIR].[_cornie_Report_Shipped_Serial_Numbers] '%" & Range("C3").Value & "%'"
End With
ActiveWorkbook.Connections("VIDIR3 Arborg_Test_App country").Refresh
End Sub
The four parameter names in the modified stored procedure are:
@BillName varchar(60)
,@ShipName varchar(60)
,@Item varchar(40)
,@FamilyCode varchar(40)