Excel VBA query recordset value from SQL SP Type Mismatch error

rs is a recordset object, comparing it to 1 is not valid, its not the value returned from the procedure.

If 1 or 2 is a value from a SELECT then to read the single value:

if rs.eof then
   msgbox "no rows"
else
   result = rs.collect(0)
   msgbox result
end if

I think doing it like this would be better.

The function inserts SQL Server data to the target Excel range using ADO.

Function ImportSQLtoRange(ByVal conString As String, ByVal query As String, _
    ByVal target As Range) As Integer

    On Error Resume Next

    ' Object type and CreateObject function are used instead of ADODB.Connection,
    ' ADODB.Command for late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library

    ' ADO API Reference
    ' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx

    ' Dim con As ADODB.Connection
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionString = conString

    ' Dim cmd As ADODB.Command
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")

    cmd.CommandText = query
    cmd.CommandType = 1         ' adCmdText

    ' The Open method doesn't actually establish a connection to the server
    ' until a Recordset is opened on the Connection object
    con.Open
    cmd.ActiveConnection = con

    ' Dim rst As ADODB.Recordset
    Dim rst As Object
    Set rst = cmd.Execute

    If rst Is Nothing Then
        con.Close
        Set con = Nothing

        ImportSQLtoRange = 1
        Exit Function
    End If

    Dim ws As Worksheet
    Dim col As Integer

    Set ws = target.Worksheet

    ' Column Names
    For col = 0 To rst.Fields.Count - 1
        ws.Cells(target.row, target.Column + col).Value = rst.Fields(col).Name
    Next
    ws.Range(ws.Cells(target.row, target.Column), _
        ws.Cells(target.row, target.Column + rst.Fields.Count)).Font.Bold = True

    ' Data from Recordset
    ws.Cells(target.row + 1, target.Column).CopyFromRecordset rst

    rst.Close
    con.Close

    Set rst = Nothing
    Set cmd = Nothing
    Set con = Nothing

    ImportSQLtoRange = 0

End Function

Code comments:

The query parameter can contain a SELECT or EXECUTE query.
The resulting data will be inserted starting from the top left cell of the target range.
Using Object types and the CreateObject function instead of direct use of ADO types
lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
This code works in Microsoft Excel 2003-2016.
Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.