Try using ADO:

http://www.erlandsendata.no/english/vba/adodao/

Examples are to Access, but should be adaptable to SQL Server.

--
Regards,
Tom Ogilvy


"gerald" wrote in message
...
I am trying to create a function that extracts data from a
Sql Server into Excel.

My current design which looks something like below, works
O.K using "QueryTables.Add" but problem is that it can
only return to a range on the worksheet, well as i want
result returned to a variable which I can manipulate
further.

Does anyone know of a way of achieving this.

Function xyz

connstring = _
"ODBC;DSN=CSTW2K.SUNDB;UID=sa;PWD="
With ActiveSheet.QueryTables.Add(
Connection:=connstring, _
Destination:=Range("a11"), Sql:=sqlstring)
.RefreshOnFileOpen = False
xlInsertDeleteCells
.Refresh
End With


End Function


N.B I tried using "SQLRequest" which returns an array but
I could not solve problem with "XLOdbc" that it kept
referring to.