Sort and ADO recordset in Excel using the SORT property

[BluByU]

I am having a problem with VB script in an Excel spreadsheet that is calling a stored procedure. The stored procedure I am calling is used by other spreadsheets and has a sorting method that I want to override. I don't want to change the stored procedure, but just change the sort order in the ADO recordset.

In order to accomplish this, I am using the "SORT" property of the recordset. I set this property to the "entry_on" field name.

Example: rs.sort = "entry_on"

When I do this, I get a 3251 runtime error:
"Current provider does not support the necessary interfaces for sorting or filtering." Any ideas on how to work around this problem? BTW: The connection is using a "SQL Server" as the provider. Thanks!

 

[SkipVought]

Hi,

1. use the CopyFromRecordset method to put the resultset on the sheet.

2. use Data > Sort as required.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[BluByU]

Thanks for your response, but is there a way that I can do this from inside ADO? If I copy the recordset to a sheet and then sort by the desired column, that column is sorted, but not the rows on the spreadsheet associated with them. So, I don't think whis will work, unless I am missing something.

Any other suggestions?

 

[SkipVought]

"...sort by the desired column, that column is sorted, but not the rows on the spreadsheet associated with them."

This statement indicates that you do not understand SORT in Excel.

If you SELECT one or more columns and SORT, then YES, the UNSELECTED contiguous rows will NOT sort.

But that is not the proper method to Sort a Table. Select ONE CELL within the table. Then Data > Sort.... The ENTIRE TABLE (all the rows of data within the table) will be sorted accordingly.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[shomila]

hi

why dont you try sorting in the SQL statement
e.g:

SELECT a, b, c
FROM tblExample
ORDER BY b;

 

[SkipVought]

shomila,

the OP does not want to change the stored procedure.

" I don't want to change the stored procedure, but just change the sort order in the ADO recordset."




Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[BluByU]

You were right Skip. I didn't have a range selected when I performed the SORT on the data column. Once I tried that, I got another error which said that the column I was sorting on was "not in range" (when it was). I then changed the format on the SORT column to an explicit date format and corrected this error.

Although I would prefer doing the sort in ADO, this approach does work, thanks for the tip. Perhaps I need a disconnected record set for this sort to work inside Excel. However, the error messages were saying that the provider didn't support this operation. Perhaps the ADO functionality inside Excel is crippled?

 

[rmikesmith]

Perhaps the ADO functionality inside Excel is crippled?

No, when you set a reference to an external library (ADO in this case), you have access to all of its properties/methods. An ADO reference I use indicates the following:
Note: This property (Sort) can only be used if the CursorLocation property is set to adUseClient. Is that the case in your setup?


Regards,
Mike

 

[SkipVought]

You're talking THEORY. Where's the code that fails? Quite often, macro recorded code uses the Selection object, and when you replay the macro with a selection that is not expected, the code craters.

GET RID OF SELECTION in your code and replace with explicitly referenced range objects.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[BluByU]

No, it's not theory. I coded all of this VBA code by hand and did not record a macro. I am using a explicity referenced range object.

Anyway, the problem is resolved, thanks.

 

[SkipVought]

THEORY, in terms of WORDS ONLY.

Where is your CODE??? Why won't you post your code???

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]