Hi Ben,

Yes it is possible.
Turn the macro recorder on before you make a query. This should give you
some code.
Below is a copy of how I execute a query on a Oracle DB. The function
StringToArray is from Microsoft as there is a problem with the length of
the SQL string .
Regards,
Jean-Yves

Option Explicit
Sub GetStudentdata()
Dim strWhat As String
Dim StrFrom As String
Dim strWhere As String
Dim strOrder As String
Dim strSql As String
Dim varSql As Variant
strWhat = "SELECT SURNAME, NICKNAME,ID_PAYROLL,OPS_SECTOR"
StrFrom = "FROM PDMS.PDMS_STAFF_MEMBERS"
strWhere = "WHERE
nvl(OPS_RECOURSE_TYPE||OPS_RECOURSE_NUM,OPS_INIT_C OURSE_TYPE||OPS_INIT_COURS
E_NUM) = '" & Range("H3").Text & Range("H6").Value & " ' "
strOrder = "ORDER BY SURNAME"
strSql = strWhat & " " & StrFrom & " " & strWhere & " " & strOrder
varSql = StringToArray(strSql)
Range("A2").Select
With Range("A2").QueryTable
.Connection = _
"OLEDB;Provider=MSDAORA.1;Password=password;Us er ID=username;Data
Source= DatabaseName"
.CommandType = xlCmdSql
.CommandText = varSql
.Refresh BackgroundQuery:=False
End With

End Sub


Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function


"Ben Rum" wrote in message
...
How do I source external data from a SQL Server stored Procedure with
parameters..

A stored procedure *without* parameters is not problem -- (get external
data - new db query - enter "exec sp_MySpName" into MS-Query)


However, with adding parameters, when the user selects "Refresh Data!" I
would like the user to be prompted to enter the values as per the stored
procedure parameters..

e.g. exec sp_MySpName @StartDate = [prompt user], @EndDate = [prompt
user]

Is this possible?