Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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)
share|improve this question

1 Answer 1

Use the logic described in http://superuser.com/questions/294779/pass-a-cell-contents-as-a-parameter-to-an-excel-query-in-excel-2007 (second part of the first answer, involving CreateParameter and the Parameters collection. See also http://www.ozgrid.com/forum/showthread.php?t=170413 for another example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.