I'm designing a report that selects an Excel sheet in an Excel book (this sheet is going to be used as a template) and fills in a series of data from a table in Navision. For each different register date I have to create a new sheet in another Excel book but based on the initial template.
I know table 370 Excel Buffer has a CreateSheet function and SaveWorkbook but I don´t know how to continue.
Thanks in advance for your help!
Follow this Post
Ok I'll take a look! Thanks once again
Hi again! Maybe I din't explain myself clear enough(sorry about that.......still new at this )!
I have an Excel workbook which has one sheet. This sheet is configured in a particular way, it acts like a template (see figure). I have to process certain data in a group of records and transfer those values to the Excel template.
For each Entry date I must add a new Sheet but that new sheet must exactly the same style as the initial template Excel sheet (Sheet 1 in example).What I do first in the OnPreReport function is create an Excel book. Production is a table I developed and rExcelBuffer is type ExcelBuffer but is a temporary table. v_iteration is to determine whether it's the first record being processed. The code is the following:
Production".SETRANGE(Production."Entry date","Starting date","Ending date");
IF Production.FINDSET() THEN BEGIN
v_iteration := FALSE;
ERROR('No entry with that date);
Then on the OnAfterGetRecord() function of the DataItem, I write the following code, where rExcelBuffer2 is also type Excel Buffer.
IF NOT v_iteration THEN BEGIN //only one record
rExcelBuffer."Cell Value as Text":=FORMAT(WORKDATE);
IF NOT v_iteration THEN BEGIN //2º iteration and so on
v_Num := 1;
Sheet := 'Sheet1';
rExcelBuffer2.OpenBook(FileName,Sheet); //This is the template sheet I was talking about
IF rExcelBuffer.FINDSET THEN REPEAT
UNTIL rExcelBuffer.NEXT = 0;
v_iteration := TRUE;
rExcelBuffer2.AddSheet; //AddSheet is a new function I added to the Excel Buffer table
Sheet := Text000;
Sheet := STRSUBSTNO(Sheet, v_Num);
As I mentioned above, AddSheet() is a function I added in the Excel Buffer table to be able to add a new sheet to the Excel workbook. The code is the following:
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.Worksheets.Add;
And finally on the OnPostDataItem() I save the workbook tsave all the changes.
Right now, the first sheet is generated correctly and the data is set where it is supposed to go in the template. With this code I also add new sheets to the workbook but they appear blank and don't inherit the template's style.
I hope that I expressed myself better.
Thanks in advance for all of your help!!
I think your resultant report ( i mean excel) is getting all the dates in one sheet. You said(each Entry date I must add a new Sheet)
if above my analysis is Right( i thought ) . i Haven't seen any Logic that checks for Dates..
sry If am wrong..
What i may have done is Make some Function such that to PrintHeader , PrintFooter instead of Using Template and i use them in OnafterGet as yourequirement needs it You can use ur template wherever i used Function (may be)...
if date = '' then begin
Date := starting date; PrintHEader; End;
if postingdate = date then
Addnewsheet; printheader; date:= posting date;
at last OnPost printfooter...
Let me know if it Helped or Not.