Unable to get Delphi Sort with Excel to Work

Question

I am writing a program in Delphi 10/Seattle to control Excel 2013. I need to do a sort based on a column. The column has headers. While my code compiles, the actual SORT command gives the error 'Could not convert variant of type (Error) into type (Boolean).' In short, I have a parameter wrong, but I can't determine WHICH parameter. I have gone through the MS syntax (URL listed below), but can't find anything wrong. Here is a working sample which shows the problem. I DID generate my own type library, which is in the USES clause.

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, ComObj, Excel_TLB;
..
procedure TForm1.Button2Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  myChart: Shape;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'F10'].value2 := 10;

   RawDataSheet.Sort.SortFields.Clear;

   // Now actually do the sort...
  // SYNTAX at https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.sort.aspx
   RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].Sort (
                                 RawDataSheet.Range['A1:A10', EmptyParam], xlAscending, // Key1, Order1
                                 EmptyParam, EmptyParam, xlAscending,  // key2, Type_, Order2
                                 EmptyParam, xlAscending,  // key3, Order3
                                 xlYes, EmptyParam, False, xlSortRows, // Header, OrderCustom, MatchCase, Orientation
                                 xlPinYin, EmptyParam, EmptyParam, EmptyParam); // Sort, Data Option1, Data Option2, Data Option3


end;

Maybe this can help: In excel start recording a macro, than do your sorting. Now you can look in the macro exactly how the command should look like. I always found this much simple and more accurate than MS help — GuidoG
– GuidoG, Commented Nov 21, 2016 at 16:32
If Guido's comment does not help, you can switch to IDispatch as same as before. "OleVariant := RawDataSheet.Range['A1', 'F10'];" "OleVariant.Sort(..". — Sertac Akyuz
– Sertac Akyuz, Commented Nov 21, 2016 at 23:27
@Sertac - The ONLY way I can get this to work is by defining both ranges as OleVariants. Please submit your comment as an answer, and I will accept. — user1009073
– user1009073, Commented Nov 23, 2016 at 13:49

Sertac Akyuz · Accepted Answer · 2016-11-23 23:43:17Z

There are more than one offending parameters. The thing what is wrong with them is they don't like EmptyParam, although it is what we should be passing for an unused optional parameter.

It is difficult to locate which one(s) because of two reasons. One is that, the way you supply parameters works quite alright when you use late binding. The other one is the misleading error message:

... EVariantTypeCastError with message 'Could not convert variant of type (Error) into type (Boolean)'.

An EmptyParam is a variant set to type varError, so first part suggests that we should suspect EmptyParams. Conversion to boolean fails, then what we'll be looking for is an EmptyParam passed for an optional boolean parameter. Unfortunately there are none, not even anything boolean-like. In fact any one of the 'DataOption' enumerations cause the above error message.

Here is a possible systematic approach that might help to find a working solution:

Use late binding first, pass as little parameters as required.
Fill all remaining parameters with EmptyParam, test, then adapt the call to early binding.
From right to left, replace unused optional parameters with actual values until you get a different error, or make it work.

Here is my working sample:

procedure TForm1.Button1Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'F1'].value2 := 'head';
   RawDataSheet.Range['A2', 'F2'].value2 := 8;
   RawDataSheet.Range['A3', 'F3'].value2 := 17;
   RawDataSheet.Range['A4', 'F4'].value2 := 4;
   RawDataSheet.Range['A5', 'A5'].value2 := 10;
     RawDataSheet.Range['B5', 'F5'].Value2 := 11;
   RawDataSheet.Range['A6', 'F6'].value2 := 7;
   RawDataSheet.Range['A7', 'F7'].value2 := 1;
   RawDataSheet.Range['A8', 'F8'].value2 := 2;
   RawDataSheet.Range['A9', 'A9'].value2 := 10;
     RawDataSheet.Range['B9', 'B9'].value2 := 11;
       RawDataSheet.Range['C9', 'F9'].value2 := 9;
   RawDataSheet.Range['A10', 'F10'].value2 := 10;

   RawDataSheet.Sort.SortFields.Clear;

   // Now actually do the sort...
  // SYNTAX at https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.sort.aspx
   RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].Sort
       (RawDataSheet.Range['A1', 'A10'], xlAscending,
        RawDataSheet.Range['B1', 'B10'], EmptyParam, xlAscending,
        RawDataSheet.Range['C1', 'C10'], xlAscending,
        xlYes, NULL, False, xlSortColumns,
        xlPinYin, xlSortNormal, xlSortNormal, xlSortNormal);
end;

Finally I'm glad that Type parameter did not complain passing an EmptyParam because I don't understand what it is.

Old answer follows:

I tested your parameters by using the IDispatch/Invoke route, late binding, it generally has a higher probability of getting to work despite loosing some performance and type safety. None of them are wrong, I also tested other keys. Although parameters are not wrong, excel throws an error if early binding is used. Here is one working example:

procedure TForm1.Button1Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  V: OleVariant;   // Range
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'F1'].value2 := 'head';
   RawDataSheet.Range['A2', 'F2'].value2 := 8;
   RawDataSheet.Range['A3', 'F3'].value2 := 17;
   RawDataSheet.Range['A4', 'F4'].value2 := 4;
   RawDataSheet.Range['A5', 'A5'].value2 := 10;
     RawDataSheet.Range['B5', 'F5'].Value2 := 11;
   RawDataSheet.Range['A6', 'F6'].value2 := 7;
   RawDataSheet.Range['A7', 'F7'].value2 := 1;
   RawDataSheet.Range['A8', 'F8'].value2 := 2;
   RawDataSheet.Range['A9', 'A9'].value2 := 10;
     RawDataSheet.Range['B9', 'B9'].value2 := 11;
       RawDataSheet.Range['C9', 'F9'].value2 := 9;
   RawDataSheet.Range['A10', 'F10'].value2 := 10;

   RawDataSheet.Sort.SortFields.Clear;
{
   // Now actually do the sort...
  // SYNTAX at https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.sort.aspx
   RawDataSheet.UsedRange[LOCALE_USER_DEFAULT].Sort (
         RawDataSheet.Range['A1:A10', EmptyParam], xlAscending, // Key1, Order1
         EmptyParam, EmptyParam, xlAscending,  // key2, Type_, Order2
         EmptyParam, xlAscending,  // key3, Order3
         xlYes, EmptyParam, False, xlSortRows, // Header, OrderCustom, MatchCase, Orientation
         xlPinYin, EmptyParam, EmptyParam, EmptyParam); // Sort, Data Option1, Data Option2, Data Option3
}
  V := RawDataSheet.Range['A1', 'F10'];
  V.Sort(RawDataSheet.Range['A1:A10', EmptyParam], xlAscending,
        RawDataSheet.Range['B1', 'B10'], EmptyParam, xlAscending,
        RawDataSheet.Range['C1', 'C10'], xlAscending,
        xlYes, EmptyParam, False, xlSortColumns,
        xlPinYin, EmptyParam, EmptyParam, EmptyParam);
end;

@user - You're welcome. I tried a little harder and came up with an update. Please have a test if you can.

Christine Ross · Accepted Answer · 2016-11-22 15:18:39Z

0

I think you had some mismatching with the required parameters. You should try

RawDataSheet.UsedRange.Sort (RawDataSheet.Range['A1:A10'], xlAscending, // Key1, Order1
                                 EmptyParam, xlAscending,  // key2, Type_, Order2
                                 EmptyParam, xlAscending,  // key3, Order3
                                 xlYes, EmptyParam, EmptyParam, xlSortRows, // Header, OrderCustom, MatchCase, Orientation
                                 EmptyParam, EmptyParam, EmptyParam, EmptyParam); // Sort, Data Option1, Data Option2, Data Option3

Remove all unclear parameter and replace them with Emptyparam, even "true" or "false" could sometimes be unclear, sometimes I had to use "wordbool(1)" or "msotrue" within Delphi code instead of true

answered Nov 22, 2016 at 15:18

Christine Ross

4635 silver badges15 bronze badges

1 Comment

user1009073 Over a year ago

It looks like you might be using a different version of Excel, as your change will not compile. Per your suggestion, I did change everything EXCEPT the sortRange to be EmptyParam. It did not fix my issue. I then changed MatchCase (which appears to be the only Boolean) to be wordbool(1). Did not help. I finally changed my sort range to be a predefined OleVariant. That did not help either. Still at a loss. I did generate VBA Macro code. All my parameters look correct, but still error out...

Collectives™ on Stack Overflow

Unable to get Delphi Sort with Excel to Work

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related