SQL.REQUEST

[Nickyboy]

I have just tried using the =sql.request function for the frist time but can't work out why the result is only occupying the cell where the formula is held. It just returns the first field from the table, as opposed to the whole table that the "select *" would normally return from SQL Server. This happens even when I specify an entire sheet as an output.

...and a Merry Xmas to all.

 

[TommyGun]

Here are a few notes from the SQL.REQUEST Help documentation.

First off, if this is called from a spreadsheet then the Output_Ref parameter is ignored.

Output_ref is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored.

Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case).

If you omit output_ref, SQL.REQUEST does not return a completed connection string.

Secondly, the result of this function is an array, so you would have to access it as such.

Return Value

If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query.

And, just a few more closing remarks from the help file.

Remarks

SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range.
If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size.
If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array.
The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic for Applications.

 

[Nickyboy]

Thanks TommyGun

I got all that from the help file, but I didn't really understand it.

I have entered one formula, in one cell, that does not reference any other cell on the worksheet. Not sure what it means by entering the array in the formula - how would I do that - would it be within the query_text it self?

Does this all mean that I have to do this as a macro to get the full result set?

Thanks again

Nick

 

[Joke]

Select the one cel wtih the formula and extend the selection to all the cells you want to include in the array

press F2 and then CTRL SHIFT ENTER

does that answer your question?
Joke