Hi all,
I’ve spent a couple of days on a complex report that generates an Excel workbook and I’m absolutely stuck with the last detail - that is to add (collapseable) subtotals to 24 columns on the main data sheet. The fact that it’s a must to have the scheme applied automatically discards the option of entering the formula directly in 2,500+ cells, which btw would look something like this (if it can be of use to anyone else - the txtVars are Range Naming helpers):
ExcelBuf.AddColumn((’=SUBTOTALES(9;’+txtRangeName+’)’),TRUE,txtRangeComment,TRUE,FALSE,FALSE,’’);
The VBA syntax of the more sophisticated Subtotal method is quite simple:
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(8, 10, 12, 14, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
where the numbers represent the column indexes.
And in NAV it would be:
XlWrkSht.Range(STRSUBSTNO(’%1:%2’,myFirstCell,myLastCell)).Subtotal(4,-4157,?,TRUE,FALSE,TRUE);
where ? is the part where I’m stuck
I’ve been unable to create the TotalList array - Navision will only accept a single column as a parameter (e.g. 8 as int). I’ve searched DUG, mibuso, Tips dBits, MSDN (+ a zillion webs) and I’ve seen similar requests allthough I haven’t found a valid solution to my case. 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, messing with the Parameter object, ehem… even passing a string!!
Does anyone have the solution to translate the VBA syntax into C/AL? Oddly the VBA Object Browser skips all info on the TotalList parameter…
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 probably because I can’t get to pass the Find method with the full parameter list (to pass Search by Column - NAVs obsession to pass parameters by order prevents me from passing Search by Column because I can’t get XlLookAt through) - it causes an exeption (of course without giving any helpful details… just good ol’ HRESULT)
I was desperate to deliver the first output of this report yesterday so I inserted subtotals in Col 8 only. Then I repeated the instruction manually in Excel with the overwrite option but in doing so, some 2,500+ named ranges disappeared (the ones I have in the odd numbered columns).
Afortunately my customer was happy with the first (manipulated) version but just thinking when they’ll ask me for a second output makes me shiver…
If any of you have any hints to share I’d really appreciate it.
Regards,
Bleika