New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Parameter-Driven SQL Query from Excel

By James Hippolite, in , posted: 27-Mar-2007 21:26

Private Sub CommandButton1_Click()

    Dim TheQuery As QueryTable

    Dim TheSheet As Worksheet

    Dim FirstLetter As String

    Dim RunDate As Date

    Dim parm As Parameter


    Set TheSheet = ActiveSheet

    With TheSheet

        FirstLetter = .Range("B1")

        RunDate = .Range("B2")

        Set TheQuery = .QueryTables(1)

    End With


    With TheQuery

        .Connection = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=(UserID);PWD=(password);DATABASE=(DBname)"


        .CommandText = "EXEC dbo.prc_SelectTest @FirstLetter=?, @RunDate=?"

        Set parm = .Parameters.Add("@FirstLetter")

        parm.SetParam xlConstant, FirstLetter

        Set parm = .Parameters.Add("@RunDate")

        parm.SetParam xlConstant, RunDate


    End With


End Sub


Other related posts:
Geek Post Monthly Newsletter Volume 2 Issue 6
Exam 70-300 Objective 3

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".