Export to 2 sheets in an excel book...

ccorreia
ccorreia Member Posts: 36
edited 2011-06-07 in NAV/Navision Classic Client
How can I put for eg. the data of Items of group A in sheet1, Items of group B in sheet2? Using Excel Buffer?

Im not understanding how I can change from one sheet to another so I can make something like that.

Some help?

Thanks!

Comments

  • Serval
    Serval Member Posts: 51
    I have not tried it. But this may works :

    - declare an automation variable :
    Name autExcelSheet
    Subtype Microsoft Excel 11.0 Object Library'.Sheets

    - use the following mehod :
    autExcelSheet.select('sheet name');
  • Arhontis
    Arhontis Member Posts: 667
    An easy approach, is to: 
    fill excel buffer lines (item category 1)...
.
.
.
//Create Excel and worksheet 1
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(...);//to fill the first sheet with the data from excel buf...

fill excel buffer lines 2 (item category 2)...
.
.
.
//Create worksheet 2
ExcelBuf.CreateSheet(...);//to fill the second sheet with the data from excel buf...

ExcelBuf.GiveUserControl;

    Studied a little report 113 (with one sheet) but it gives an idea...

    Haven't tried it but it is a little simple and I think it will work...

    More complex functionality, but handy is to study cu 424 that exports an analysis view with multiple sheets...

    [Edit]
    Of course you can always do some additional search on the forums about excel buffer to get more ideas... :)
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • ccorreia
    ccorreia Member Posts: 36
    hum, I guess I have to check CU424, but dont seems easy :S
    I can add info in one sheet in one book, or open 2 books and insert 1 sheet with info in each...
    but create only one book with 2 sheets with info still cant do...

    or I can add a sheet with info and the other without nothing...
  • Arhontis
    Arhontis Member Posts: 667
    Sample code to open excel, create book, delete all sheets excel one, create a second one and put some values to different sheets... 
    Variables:
Name	DataType	Subtype	Length
XLApp	Automation	'Microsoft Excel 11.0 Object Library'.Application	
XlWorkBook	Automation	'Microsoft Excel 11.0 Object Library'.Workbook	
XlWorkSheet	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWorkSheet2	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWrkshts	Automation	'Microsoft Excel 11.0 Object Library'._Worksheet	

IF NOT CREATE(XLApp,TRUE) THEN
  ERROR('Excel not found.');
XLApp.Visible(TRUE);
XlWorkBook := XLApp.Workbooks.Add;
i := 1;
EndOfLoop := XlWorkBook.Worksheets.Count-1;
WHILE (i <= EndOfLoop) DO BEGIN
  XlWrkshts := XlWorkBook.Worksheets.Item(1);
  XlWrkshts.Delete;
  i := i + 1;
END;
XlWorkSheet := XLApp.ActiveSheet;
XlWorkSheet.Name :='I AM SHEET 1';
XLApp.ActiveWorkbook.Sheets.Add();
XlWorkSheet2 := XLApp.ActiveSheet;
XlWorkSheet2.Name :='I AM SHEET 2!';
FOR i:=1 TO 10 DO
  XlWorkSheet.Range('A'+FORMAT(i)).Value := 'Hello sheet 1 from value '+FORMAT(i);
FOR i:=1 TO 10 DO
  XlWorkSheet2.Range('A'+FORMAT(i)).Value := 'Hello sheet 2 from value '+FORMAT(i);

    So use XlWorkSheet as the first, and XlWorkSheet2 as the second to post your values... :)

    I think that is what you want, right?
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • ccorreia
    ccorreia Member Posts: 36
    Im using Excel Buffer, and I have something like this:
    IF CreatedBook THEN BEGIN
TempExcelBuffer.CreateSheet(Item."No.",'',COMPANYNAME,USERID);
END ELSE BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Item."No.",'',COMPANYNAME,USERID);
CreatedBook:=TRUE;
END;
TempExcelBuffer.GiveUserControl;
TempExcelBuffer.RESET;

    And I get the error:
    "This message is for C/AL Programmers:
    This Automation variable has not been instantiated.
    You can instantiate it by either creating or assigning it."
    Pointing to
    XlWrkSht.Name := SheetName;
    at CreateSheet() of Table 370 (Excel Buffer)

    So I create the book first, then I insert the first sheet, when I try to insert the second sheet I get that error, for what I think Im seeing it seems that he losts the created book variables and so...
  • Arhontis
    Arhontis Member Posts: 667
    It is the GiveUserControl routine that has the clear, so don't call it just yet. only when you are really done. look at the routine in the Excel Buffer table: 
    GiveUserControl()
  XlApp.Visible(TRUE);
  XlApp.UserControl(TRUE);
  CLEAR(XlApp);
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • ccorreia
    ccorreia Member Posts: 36
    Arhontis wrote:
    It is the GiveUserControl routine that has the clear, so don't call it just yet. only when you are really done. look at the routine in the Excel Buffer table: 
    GiveUserControl()
  XlApp.Visible(TRUE);
  XlApp.UserControl(TRUE);
  CLEAR(XlApp);

    I see ](*,)
    Even so now Im trying to put the use of the function only at the end of processing all, and even so Im having a
    "This message is for C/AL Programmers:
    This Automation variable has not been instantiated.
    You can instantiate it by either creating or assigning it."
    related with the use of that function... pointing to the: "XlApp.Visible(TRUE);"

    I really have to think were's the right place for it.

    But im close, thanks to your help ;)
  • Arhontis
    Arhontis Member Posts: 667
    What are you using for exporting? Report? CU? Form?
      The main concept: 1) Fill the buffer lines with the desired items. 2) Call ExcelBuf.CreateBook and ExcelBuf.CreateSheet(...). 3) Empty the Excel Buffer recs. 4) Fill the buffer lines with the desired items (second category). 5) Call ExcelBuf.CreateSheet(...). 6) Call ExcelBuf.GiveUserControl;
    Did you see the code sample above for manual exporting without using the Excel Buffer?[/list]
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • ccorreia
    ccorreia Member Posts: 36
    ccorreia wrote:
    Im using Excel Buffer, and I have something like this:
    IF CreatedBook THEN BEGIN
TempExcelBuffer.CreateSheet(Item."No.",'',COMPANYNAME,USERID);
END ELSE BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Item."No.",'',COMPANYNAME,USERID);
CreatedBook:=TRUE;
END;
TempExcelBuffer.GiveUserControl;
TempExcelBuffer.RESET;

    And I get the error:
    "This message is for C/AL Programmers:
    This Automation variable has not been instantiated.
    You can instantiate it by either creating or assigning it."
    Pointing to
    XlWrkSht.Name := SheetName;
    at CreateSheet() of Table 370 (Excel Buffer)

    So I create the book first, then I insert the first sheet, when I try to insert the second sheet I get that error, for what I think Im seeing it seems that he losts the created book variables and so...

    Nope, I think I already put the GiveUserControl function in the right place and I still have the problem Im quoting...

    My report has something like this:
    DataItem Name
    Item <Item>
    Interf. ATX-Production <Interf. ATX-Production>
    Interf. ATX-Consumption <Interf. ATX-Consumption>

    Im calling the giveusercontrol function on the Item DataItem at Item - OnPostDataItem() and the other code is at Interf. ATX-Consumption in Interf. ATX-Consumption - OnPostDataItem() also.
  • ccorreia
    ccorreia Member Posts: 36
    Arhontis wrote:
    What are you using for exporting? Report? CU? Form?
      The main concept: 1) Fill the buffer lines with the desired items. 2) Call ExcelBuf.CreateBook and ExcelBuf.CreateSheet(...). 3) Empty the Excel Buffer recs. 4) Fill the buffer lines with the desired items (second category). 5) Call ExcelBuf.CreateSheet(...). 6) Call ExcelBuf.GiveUserControl;
    Did you see the code sample above for manual exporting without using the Excel Buffer?[/list]

    Im using a report.
    About the concept.
    1) buffer lines already been filled.
    2) calling the createbook and createsheet (with one book and 1 sheet no problem)
    3) clearing buffer recs (TempExcelBuffer.DELETEALL; CLEAR(TempExcelBuffer);)
    4) i put again the values
    5) call only createsheet after book created
    6)call the giveusercontrol only at end...
  • Arhontis
    Arhontis Member Posts: 667
    Are you sure that the CLEAR(TempExcelBuffer); doesn't do the damage? :sick:
      * Build the Excel buffer lines on the OnAfterGetRecord of each child dataitem. * Call TempExcelBuffer.CreateBook; and TempExcelBuffer.CreateSheet(...) in the OnPostDataItem of the first child dataitem. * Call TempExcelBuffer.DELETEALL; in the OnPreDataItem of the second dataitem. * Call TempExcelBuffer.CreateSheet(...) in the OnPostDataItem of the second child dataitem. * Place the GiveUserControl in the Report - OnPostReport.
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • gison
    gison Member Posts: 128
    Arhontis wrote:
    Sample code to open excel, create book, delete all sheets excel one, create a second one and put some values to different sheets... 
    Variables:
Name	DataType	Subtype	Length
XLApp	Automation	'Microsoft Excel 11.0 Object Library'.Application	
XlWorkBook	Automation	'Microsoft Excel 11.0 Object Library'.Workbook	
XlWorkSheet	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWorkSheet2	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWrkshts	Automation	'Microsoft Excel 11.0 Object Library'._Worksheet	

IF NOT CREATE(XLApp,TRUE) THEN
  ERROR('Excel not found.');
XLApp.Visible(TRUE);
XlWorkBook := XLApp.Workbooks.Add;
i := 1;
EndOfLoop := XlWorkBook.Worksheets.Count-1;
WHILE (i <= EndOfLoop) DO BEGIN
  XlWrkshts := XlWorkBook.Worksheets.Item(1);
  XlWrkshts.Delete;
  i := i + 1;
END;
XlWorkSheet := XLApp.ActiveSheet;
XlWorkSheet.Name :='I AM SHEET 1';
XLApp.ActiveWorkbook.Sheets.Add();
XlWorkSheet2 := XLApp.ActiveSheet;
XlWorkSheet2.Name :='I AM SHEET 2!';
FOR i:=1 TO 10 DO
  XlWorkSheet.Range('A'+FORMAT(i)).Value := 'Hello sheet 1 from value '+FORMAT(i);
FOR i:=1 TO 10 DO
  XlWorkSheet2.Range('A'+FORMAT(i)).Value := 'Hello sheet 2 from value '+FORMAT(i);

    So use XlWorkSheet as the first, and XlWorkSheet2 as the second to post your values... :)

    I think that is what you want, right?

    Hi all,
    I got a problem for the statement "XLApp.Visible(TRUE)".
    If I don't wanna launch Excel program now, I think we can set the parameter to False.
    My problem is if I set parameter False then there is a Excel process still in the Windows Task Manager while I finish the the export job.
    Do you know how to close the process? :?
  • Arhontis
    Arhontis Member Posts: 667
    Hi,

    I always start with false and when I finish the process I use TRUE to show XL...

    The reason is that if the xl is shown while exporting, it is slower and the user might start clicking on it, and it will mess up the data...

    So,

    XLApp.Visible(FALSE);
    .
    .
    <processing>
    .
    .
    .
    XLApp.Visible(TRUE);

    If you skip the TRUE, then the only way is to "End Task" via "Task Manager"...
    Regards,
    Arhontis

    https://forum.mibuso.com/search
  • gison
    gison Member Posts: 128
    Arhontis wrote:
    Hi,

    I always start with false and when I finish the process I use TRUE to show XL...

    The reason is that if the xl is shown while exporting, it is slower and the user might start clicking on it, and it will mess up the data...

    So,

    XLApp.Visible(FALSE);
    .
    .
    <processing>
    .
    .
    .
    XLApp.Visible(TRUE);

    If you skip the TRUE, then the only way is to "End Task" via "Task Manager"...

    Thanks for your suggestion. :o
  • gison
    gison Member Posts: 128
    Arhontis wrote:
    Sample code to open excel, create book, delete all sheets excel one, create a second one and put some values to different sheets... 
    Variables:
Name	DataType	Subtype	Length
XLApp	Automation	'Microsoft Excel 11.0 Object Library'.Application	
XlWorkBook	Automation	'Microsoft Excel 11.0 Object Library'.Workbook	
XlWorkSheet	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWorkSheet2	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	
XlWrkshts	Automation	'Microsoft Excel 11.0 Object Library'._Worksheet	

IF NOT CREATE(XLApp,TRUE) THEN
  ERROR('Excel not found.');
XLApp.Visible(TRUE);
XlWorkBook := XLApp.Workbooks.Add;
i := 1;
EndOfLoop := XlWorkBook.Worksheets.Count-1;
WHILE (i <= EndOfLoop) DO BEGIN
  XlWrkshts := XlWorkBook.Worksheets.Item(1);
  XlWrkshts.Delete;
  i := i + 1;
END;
XlWorkSheet := XLApp.ActiveSheet;
XlWorkSheet.Name :='I AM SHEET 1';
XLApp.ActiveWorkbook.Sheets.Add();
XlWorkSheet2 := XLApp.ActiveSheet;
XlWorkSheet2.Name :='I AM SHEET 2!';
FOR i:=1 TO 10 DO
  XlWorkSheet.Range('A'+FORMAT(i)).Value := 'Hello sheet 1 from value '+FORMAT(i);
FOR i:=1 TO 10 DO
  XlWorkSheet2.Range('A'+FORMAT(i)).Value := 'Hello sheet 2 from value '+FORMAT(i);

    So use XlWorkSheet as the first, and XlWorkSheet2 as the second to post your values... :)

    I think that is what you want, right?

    Hi Arhontis,
    Do you know how to copy template of sheet 1 to a new sheet while we add a new sheet. :?
    thank you.
  • Hanen
    Hanen Member Posts: 281
    Hello,

    I have a report and I want to have a workbook excel with 3 sheets and I am using the ExcelBuffer table.
    I followed what is mentionned here step by step but it didn't work :cry:

    Anyone can help me???? :oops:
    Regards

    Hanen TALBI
  • Hanen
    Hanen Member Posts: 281
    Hi,

    I'm using only one dataitem and I want to export to three excel sheets using filters soomething like if rec1.Field = 'A' then Data will be exported into sheet1 else if rec1.Field='B' then data will be exported to sheet2 else exported to sheet3 :-k
    Help!!!! :oops:
    Regards

    Hanen TALBI
  • Sog
    Sog Member Posts: 1,023
    http://www.mibuso.com/forum/viewtopic.php?f=32&t=45632
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Hanen
    Hanen Member Posts: 281
    Is there any code samples on how to do with integervalues row1 / row2?
    :oops:
    Regards

    Hanen TALBI

Categories