export to excel and order

xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

Reference:
http://msdn.microsoft.com/library/defau ... 076391.asp

There's something broken about the syntax for sorting by multiple columns at the same time:

http://support.microsoft.com/default.as ... -us;229107

However, there is a trick that may work, implied by this reference:

http://support.microsoft.com/default.as ... -us;268007

This suggests that you achieve a multi-column sorting one column at a time, provided you sort the range starting with the 'least significant' column, and progressing to the 'most significant.'

In your example then, you would do the following:

xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6'));
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

As for sub-totals, there is a 'SubTotal' method that applies to a Range, but I haven't figured out how to call it from C/AL (yet...!). The SubTotal method requires three parameters -- the first and second are integers, but the third is an Array (COM type = SAFEARRAY). Unfortunately, C/AL does not support the conversion of C/AL arrays into COM SAFEARRAYs, so there is no easy way to provide this third required parameter.

When C/AL fails, the usual solution is to create a macro in the spreadsheet that will do the work. Then, from C/AL you can invoke the macro using xlApp.Run('MyMacroName');

Let me see if I understand your problem:

You have created a sheet with values in the range a6:ad600. You want to sort the rows in that range, first by column B, then by column A. If you were using the Excel user interface, you would first highlight the entire range a6:ad600, then click on the Excel menu item 'Data, Sort...'. In the Sort dialog box, in the 'Sort by' box, you would select 'Column B', and in the 'Then by' box, you would select 'Column A'.

If you do this using the Excel user interface, do you get the results you want?

If that is correct, then you can get the same result by sorting the range twice:

• First, sort by column A (only)
• Then, sort by column B (notice that you have to do the 'separate' sorts in the reverse order)

That is why I recommended the following code:

xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6')); 
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

Look at my suggestion carefully -- first column A, then column B -- I know this seems 'backwards', but I'm pretty sure it gets the job done.