Query SQL Server from Excel with parameters

Question

What I'm trying to do is use a pull information from SQL server into an excel spreadsheet using a query with parameters.

I've been able to query the database from excel but have not been able to figure out how to add parameters.

I need the parameter to be taken from a cell in the sheet.

Sub DataExtract()

Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE= MyServer ;INITIAL CATALOG = MyDatabase ;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cnPubs.Open strConn
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs

.ActiveConnection = cnPubs

.Open "SELECT * FROM [MyTable] WHERE ColA = ?"

Sheet1.Range("B12").CopyFromRecordset rsPubs

.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub

any help on how I could simply have the question mark in the query reference a cell in the sheet would be greatly appreciated. Thanks

Tim Williams · Accepted Answer · 2014-03-28 16:29:29Z

1

Untested:

Sub DataExtract()

Dim cmd as ADODB.Command, param as Object, val
Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim strConn As String

    Set cnPubs = New ADODB.Connection

    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE= MyServer ;INITIAL CATALOG = MyDatabase ;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    cnPubs.Open strConn

    Set cmd = New ADODB.Command
    cmd.CommandText = "SELECT * FROM [MyTable] WHERE ColA = ?"
    cmd.CommandType = adCmdText


    val = Sheet1.Range("A1").Value 'query value to be used
    'create and add the parameter
    Set param = cmd.CreateParameter("", adVariant, adParamInput, 0, val)
    cmd.parameters.Append param

    Set cmd.ActiveConnection = cnPubs

    Set rsPubs = cmd.Execute()

    Sheet1.Range("B12").CopyFromRecordset rsPubs

End Sub

answered Mar 28, 2014 at 16:29

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

4 Comments

Tim Williams Over a year ago

I use that successfully with Oracle (not an Access user) - not familiar with SQL server. I do see plenty of examples using ? with SQL Server though.

Brad Over a year ago

yeah, I guess the ADO driver figures it out. The naming comes in if you want to define your parameters in any order. I'm surprised you haven't used Access though

Tim Williams Over a year ago

@Brad - I wouldn't say I've "never" used Access, but we're more of an Oracle shop here.

user1838821 Over a year ago

Works perfectly! I realise how simple a question it is but i just could not wrap my head around it until now. Thanks!

Collectives™ on Stack Overflow

Query SQL Server from Excel with parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related