How to Create multiple sheets in excel?

chandrurec
Member Posts: 560
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.
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.
0
Comments
-
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.0 -
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.0 -
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 is0 -
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.0 -
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)0 -
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.0 -
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.0 -
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.0 -
Microsoft Certified IT Professional for Microsoft Dynamics NAV
Just a happy frood who knows where his towel is0 -
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.0 -
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 is0 -
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.0 -
Hi dans,
Got it I cleared row and cols and now its working fine.
Thank you very much.
Thanks & Regards,
chandru.0 -
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? Thanks0 -
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.0
-
can I upload excel multiple file Gateway Dashboard like Transactional SMS1
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions