Has anybody created a report to export to excel then been able to create subtotals on the data, automatically from within navision, using Automation Controls?
I have got as far as creating the Subtotals on one column using the following code :-
This will group by Column 3 and subtotal Column 5, trouble is this parameter needs a VB Array and i don’t know how to generate that or syntactically how to populate it.
Thought about that, do you know if it would give the same excel functionality of opening and closing the subtotals with the levels on the lefthand side?
No the indenting it wont do liek this, that one I never worked on hard enought ot figure it out.
By the way the way I do subtotals or any formulas in Navision, is I create base XL Automation, and create a spread sheet, then in excel, insert rows and manually create the totals. Then I ust copy past the formulas form Excel into Navision, and the just count the rows and insert the appropriate row numbers, something like:
then just put that into the field as text. Of course its easier if you use fixed columns and don’t need to try to calculate Columns in excel, but even that is not difficult.
What I do if I need to get some extra things working in Excel or Word is use the Macro Recorder.
I’ll open Excel start the Macro recorder, then do what I want, stop the recording. Look at the VBA code and try and copy it in the Navision Code.
If you were to use the XL Buffer table you would have all the Column and row lengths, and you could use this to create the Formula’s as David Suggested. XLbuffer maintains the Row and column in both Numeric and Alpha form.
Thanks for the responses, that exactly what i do also, but it is just the VB Code for the array of columns to subtotal that i can nor replicate in navision.
I could create the subtotals myself through navision code also, as i know where to insert the subtotal rows, but the client wishes to have the expand/Colapse functionality that you get in excel when using the excel subtotalling.