Can't create subtotals in Excel from Navision.

Shurik

Hi,
I want to use Excel function "Subtotal" from Navision, but problem is here:
When i write
sheet.range('A9:U18').subtotal(4,-4157,'6'); all work properly. column F have a subtotals.

But I need subtotals in columns F & G.
sheet.range('A9:U18').subtotal(4,-4157,'6,7'); doesn't work

as i understand Excel want ARRAY in parameters
sheet.range('A9:U18').subtotal(4,-4157,ARRAY);

Excel macro:
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

But how do this from Navision...
arr[1]:=5; try arr as Variant,as Integer, as Code
arr[2]:=6;
sheet.range('A9:U18').subtotal(4,-4157,arr); - nothing....
:evil:

Is anybody can help?

i4tost

Try to use semicolon (;) instead of comma (,), means write '6;7' instead of '6,7'. I did not try

koga

Tried to use a ',' and a ';' but I was not succesful.


Has anyone an example how to use it in Navision, with a subtotal on more than one column ?

uto

So, how about this syntax?
sheet.range('A9:U18').subtotal(4,-4157,(6,7));

koga

Also this syntax was unsuccessful.

Tinius

Yes I have the same problem too

Rko

I have spent maybe 1 day to solve this problem, but without succes. It will be much quicker send to excel whole report line or more cells in on time using array.

If somebody know how to do it, let me know.

thaug

Try something like this:

xlWorkSheet.Range('A1').Offset(RowNo,ColumnNo).FormulaR1C1 := STRSUBSTNO('=SUBTOTAL(%1,R[-%2]C:R[-1]C)',x,RowNo);

RowNo is the number of rows from A1, ColumnNo is the same.
x is the SUBTOTAL code, for a sum that code would be the integer 9.

Preceding this code, I put in a for loop to cover as many columns as I want.

So my code (for a spreadsheet with 15 summable columns) looks something like this:

x: = 9;
RowNo := [some row number]

FOR ColumnNo := 1 TO 15 DO BEGIN
  xlWorkSheet.Range('A1').Offset(RowNo,ColumnNo).FormulaR1C1 := STRSUBSTNO('=SUBTOTAL(%1,R[-%2]C:R[-1]C)',x,RowNo);
END;

eborggrech

The issue is to create an array that is compatible the the Subtotal Function.

I resolved this issue by generating the array using inline VBScript.

In the following code, the VBScript is of type Automation 'Microsoft Script Control 1.0'.ScriptControl

Tested and works successfully.

PROCEDURE CalculateSubtotals@1000000006(GroupByColNo@1000000000 : Integer;TotalColNo@1000000001 : Text[1024]);
    VAR
      XlSum@1000000002 : Integer;
      XlCount@1000000003 : Integer;
      VBScript@1000000008 : Automation "{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC} 1.0:{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}:'Microsoft Script Control 1.0'.ScriptControl";
      VBScriptCode@1000000007 : Text[1024];
      EnterChar1@1000000006 : Text[1];
      EnterChar2@1000000005 : Text[1];
      VBEvaluationCode@1000000004 : Text[1024];
    BEGIN
      //Prepare Parameters for excel function
      XlSum := -4157;
      XlCount := -4112;
      EnterChar1[1] := 13;
      EnterChar2[1] := 10;

      IF NOT ISCLEAR(VBScript) THEN
      CLEAR(VBScript);
      CREATE(VBScript);


      VBScript.Language := 'VBScript';

      VBEvaluationCode :=  'Function SplitFunction' + EnterChar1 + EnterChar2;
      VBEvaluationCode += 'Dim SubTotalArray' + EnterChar1 + EnterChar2;
      VBEvaluationCode += STRSUBSTNO('SubTotalArray = Split("%1",",")', TotalColNo) + EnterChar1 + EnterChar2;
      VBEvaluationCode += 'SplitFunction = SubTotalArray' + EnterChar1 + EnterChar2;
      VBEvaluationCode += 'End Function';

      VBScript.AddCode(VBEvaluationCode);

      XlSubTotalRange.Subtotal(GroupByColNo,XlSum,VBScript.Eval('SplitFunction'),TRUE);
  
    END;