How to Create multiple sheets in excel?

chandrurecchandrurec Posts: 549Member
Hi all,

I want to create multiple sheets in Excel. So i am writing the code as follows.


ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text002,Text001,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ExcelBuf.CreateSheet('TestSheetCreated',Text001,COMPANYNAME,USERID);

When I run the printtoexcelo, I am having three tabs one with information,then TestSheetCreated with sames contents as in information and then Data .

But I want to have as Information,Data,TestSheetCreated.

The second question is how to insert datas into respective sheets.Is there any function available in Excel Buffer to for inserting datas to respective sheets.

Kindly let me know if anyone already done this.

Thanks in advance.

Regards,
chandru.

Comments

  • SogSog Posts: 1,023Member
    Hi Chandru,

    First off I want to comment on the excel.giveusercontrol, you should put that one as last in your code, because you're showing excel to the user before you've inserted all data.

    Next is the standard way excel works: inserting a new sheet will be inserted before the current sheet unless specified otherwise. This might be an extra parameter for createsheet.

    As last is that the standard excel buffer does not support multiple sheets. It's build in a way that you have 2 sheets maximum: a datasheet and an information sheet, usually filled in with the report name and filters.
    However, with 1 or 2 extra functions, support for multiple sheets is rather easy to implement. An other possiblility is to redesign the Excel buffer table with a sheetname, so you'll be able to put all the data in the buffer first. But that might break some reports. I'll leave the details of that design to you, since you know what you want to achieve.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • chandrurecchandrurec Posts: 549Member
    Hi Sog,

    Now I have modified the code as shown below.

    ExcelBuf.CreateBook;
    ExcelBuf.CreateSheet('TEst1',Text001,COMPANYNAME,USERID);
    ExcelBuf.CreateSheet(Text002,Text001,COMPANYNAME,USERID);
    ExcelBuf.GiveUserControl;

    But now whats happening is Information and Date sheets have the report name etc and only the last sheet Test1 has values.

    Is there any function which tell the system thats in a particular sheet, insert the values.

    Thanks & regards,
    chandru.
  • dansdans Posts: 148Member
    you need to select which excel sheet you want to use before inserting the values.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • chandrurecchandrurec Posts: 549Member
    Hi dans,

    I am creating the book in the on prereport.

    Then I have added the followind code in the on presection of customer and Vendor dataitem.


    IF PrintToExcel THEN
    BEGIN
    MakeExcelDataHeader;
    ExcelBuf.CreateSheet('Test1',Text001,COMPANYNAME,USERID);

    END;


    IF PrintToExcel THEN
    BEGIN
    MakeExcelDataHeader;
    ExcelBuf.CreateSheet('Test2',Text001,COMPANYNAME,USERID);

    END;

    and then I am calling Giveusercontrols in On Post trigger of report.

    When I run printoexcel only the last sheet is getting created and not the first sheet.

    can u tell me where the problem lies?

    Thanks & regards,
    chandru.
  • SogSog Posts: 1,023Member
    What does the debugger tell you?
    And please keep in mind that Excel buffer without modification WILL NOT SUPPORT MULTIPLE SHEETS. (except the info sheet and as mentioned in my previous post)
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • chandrurecchandrurec Posts: 549Member
    Hi Sog,

    It is not giving any error but only one sheet is getting created and the values in both the sheets are getting populated in a single sheet.

    can u tell me how to modify the excel buffer in order to support multiple sheets.

    Thanks & Regards,
    chandru.
  • SogSog Posts: 1,023Member
    So you use only the debugger when an error pops up?
    How about you try to use it to identify the reason why something doesn't work as expected?
    That way you can solve a lot of problems yourself and you don't have to wait for us to do it for you.
    As for code, yes I can and no I won't. If I give you the solution you won't have learned anything and when you face a similar problem, you'll be back.

    And did you even try to search the forum? I found a code example in seconds.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • chandrurecchandrurec Posts: 549Member
    Hi Sog,

    I searched the forum but it was given using automation and not using excel buffer but I have done using excel buffer. So if you know the exact change required for creating multiple sheets,Kindly let me know.

    Thanks in advance.

    Regards,
    chandru.
  • dansdans Posts: 148Member
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • chandrurecchandrurec Posts: 549Member
    Hi dans,

    I have added a new function named AddSheet and I am using this function before createsheet.Now 2 sheets are getting generated one for custromer and one for vendor.

    But the problem is in the Customer Sheet, the customer records are getting inserted from the beginning but in Vendor Sheet, the Vendor records are inserted after some gap in the beginning and this gap .

    Can you tell me how to get the records inserted from the beginning in both the sheets.

    Thanks in advance.

    Regards,
    chandru.
  • dansdans Posts: 148Member
    reset your col and row counter when you move to the new sheet, or use different variables.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • chandrurecchandrurec Posts: 549Member
    Hi dans,

    I have used ExcelBuf.deleteall before going to the next sheet but still the space is there in next Sheet. For reseting rows and columns is there any function or how to do that.

    Please assist me in this.

    Thanks & regards,
    chandru.
  • chandrurecchandrurec Posts: 549Member
    Hi dans,

    Got it I cleared row and cols and now its working fine.

    Thank you very much.

    Thanks & Regards,
    chandru.
  • ebitasebitas Posts: 71Member
    Chandrurec;
    I'm trying to do the same thing where I want to export to Excel and Create multi sheets, will you please share your solution with me? Thanks
  • NatashawilliamsNatashawilliams Posts: 2Member
    I am Also trying to learn the Excel to send Transactional SMS through Excel and generate reports with multiple sheet Thank you for starting this topic.
  • priyakapoorjkpriyakapoorjk Posts: 1Member
    edited 2019-05-20
    can I upload excel multiple file Gateway Dashboard like Transactional SMS
Sign In or Register to comment.