How to Create multiple sheets in excel?

chandrurec

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.

Sog

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.

chandrurec

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.

dans

you need to select which excel sheet you want to use before inserting the values.

chandrurec

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.

Sog

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)

chandrurec

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.

Sog

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.

chandrurec

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.

dans

search.php?keywords=excel+buffer+new+sheet&terms=all&author=&sc=1&sf=all&sk=t&sd=d&sr=topics&st=0&ch=300&t=0&submit=Search+the+forum

chandrurec

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.

dans

reset your col and row counter when you move to the new sheet, or use different variables.

chandrurec

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.

chandrurec

Hi dans,

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

Thank you very much.

Thanks & Regards,
chandru.

ebitas

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

Natashawilliams

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.

priyakapoorjk

can I upload excel multiple file Gateway Dashboard like Transactional SMS