NAV 4SP3 Excel97 automation -> Subtotal/Array problem

Bleika

Hi all,

I've made a new report to generate an Excel workbook and I must add (collapseable) subtotals to 24 columns.

The VBA syntax is quite simple:

Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(8, 10, 12, 14, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

However, I've been unable to create the TotalList array - Navision will only accept one total column argument (e.g. 8 as int). I've searched the forum (and other webs) and I've seen some suggestions like inserting formulas by code into every subtotal field needed. The problem is that by doing so the scheme won't work (a must in this case so the user can collapse the data in this huge file according to the GroupBy field).

I've almost tried every possible syntax (obviously not all because the problem is still there ](*,) . That includes every possible combination of single & double quotes & parenthesis, creating an int array, ehem... even passing a string!! :-$

Does anyone have the solution to translate the VBA syntax into C/AL?

I've even tried with just one column subtotalled and then copy & paste the formula - it almost works - it just pastes it in the wrong cells :oops: probably because I can't pass the Find method with the full parameter list (to pass Search by Column) - it causes an exeption (of course without giving any helpful details...)

To really finish me off, my customer still uses the Excel 8 Library (VBA help not installed) whereas I use 12...

If any of you don't like the idea of a lady going bald (me when the sun comes up in a couple of hours :roll: ) and has a possible solution - please let me know.

Thanks in advance,
B

eborggrech

An old post, but will still post the resolution for this issue

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;

smickowitz

Dear eborggrech


I was thrilled to find your solution to this problem. I am struggling with a similar problem, and even though I have tried to implement the same solution as yours, I still cant make it work. Maybe you can pinpoint the problem.?

I try to use the Querytable.Add function, but with setting the property: TextFileColumnDataTypes I still get this error :

---

Microsoft Dynamics NAV Classic

---

This message is for C/AL programmers:

An exception was raised in method TextFileColumnDataTypes. The OLE control or Automation server has returned error (HRESULT) -2147352567.
The component did not provide the exception description.

---

OK

---

My code is this:

OBJECT Codeunit 50073 Excel test
{
  PROPERTIES
  {
    OnRun=BEGIN

            ConvertCSVtoExcel('c:\temp\from.dat','c:\temp\to.xls');
          END;

  }
  CODE
  {

    PROCEDURE ConvertCSVtoExcel@1000000007(FileName@1000000001 : Text[250];OutFileName@1000000000 : Text[250]);
    VAR
      Connection@1000000002 : Text[250];
      Appl@1000000005 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00024500-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Application";
      WrkBk@1000000006 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00020819-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Workbook";
      WrkSht@1000000007 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{000208D8-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'._Worksheet";
      Qt@1000000009 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00024428-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'._QueryTable";
      R@1000000011 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Range";
      VBScript@1000000012 : Automation "{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC} 1.0:{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}:'Microsoft Script Control 1.0'.ScriptControl";
      VBEvaluationCode@1000000013 : Text[1024];
      cr@1000000014 : Text[1];
      lf@1000000015 : Text[1];
    BEGIN

      IF EXISTS(OutFileName) THEN
        ERASE(OutFileName);
      IF NOT CREATE(Appl,TRUE,TRUE) THEN
        EXIT;
      Appl.Visible(TRUE)          ;

      WrkBk := Appl.Workbooks.Add;
      WrkSht := WrkBk.Worksheets.Add;

      R:= WrkSht.Range('A' + FORMAT(1));

      Connection := STRSUBSTNO('TEXT;%1',FileName);
      Qt := WrkSht.QueryTables.Add(Connection,R);

      IF NOT ISCLEAR(VBScript) THEN
        CLEAR(VBScript);
      CREATE(VBScript);
      VBScript.Language := 'VBScript';
      cr[1]:= 13;
      lf[1] := 10;
      VBEvaluationCode :=  'Function Sps ' + cr + lf;
      VBEvaluationCode += 'Dim arr ' + cr + lf;
      VBEvaluationCode += 'arr=split("2_2_2","_") ' +cr+lf;
      VBEvaluationCode += 'Sps = arr ' + cr + lf;
      VBEvaluationCode += 'End Function';
      VBScript.AddCode(VBEvaluationCode);

      Qt.Name := 'data';
      Qt.TextFileParseType:=1;//xlDelimited, default
      Qt.TextFileOtherDelimiter:='_';
      Qt.TextFileColumnDataTypes := VBScript.Eval('Sps');
      Qt.Refresh;

      WrkBk := Appl.ActiveWorkbook;

      WrkBk.SaveAs(OutFileName,-4143,'','',FALSE,FALSE);
      WrkBk.Close(FALSE);
      Appl.Quit;
      CLEAR(Appl);
    END;

    BEGIN
    END.
  }
}

PS. I use "_" for fielddelimiter in my sourcefile, looking like this in the first line: 01_0400_10_

My aim is to avoid that the leeding zero's are not removed when converting the file to xls.

I hope you can help :-k


I found the solution now on a related post : viewtopic.php?f=23&t=44437&start=0
Thanks Tayamarn!!