Export to Excel using Excel Buffer to Populate Data over Various worksheets in a single workbook

Business Central/NAV Developers Forum
1

Hi All,

I have gone through a couple of Posts in this very same forum regarding Exporting to Excel over various worksheets,As one of the posts had suggested to add a new function into the Table 370 i.e., Excel Buffer to perform this operation. I have indeed added that function in Table 370 . which is as follows :

AddNewWorksheet( )
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.Worksheets.Add( );

Now I have used this function in my report as follows:

TempExcelBuffer.SetUseInfoSheed;
IF Firstexcelsheet THEN BEGIN
TempExcelBuffer.AddNewSheet;
TempExcelBuffer.CreateSheet(“Sheet no. 2”,’’,COMPANYNAME,USERID);
END ELSE BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(“Sheet no. 2”,’’,COMPANYNAME,USERID);
Firstexcelsheet := TRUE;
END;
TempExcelBuffer.GiveUserControl;

Now,when i run this report the very first excel sheet gets created but then the Execution stops throwing an error like this:

" This Automation Variable has not been instantiated. You can instantiate it by either creating or assigning it"

This error is pointing towards the new function that is called from the table 370, i.e., AddNewSheet.

Can you poeple help me out in this please as i am stuck on this issue for quite some time.

Thank you all in advance,

Prashanth Sanath

2

Hi Prashanth,

I have a similar solution, and it works fine.
I wonder why you do…

XlWrkBk := XlApp.ActiveWorkbook;

I don’t have to do this, and it works for me. [:)]
As long as you always use the same instance of Excel Buffer-table in your report, then XlWrkBk already has the active workbook, from the CreateSheet-function.

3

Hi Alexander,

Well i do Agree with you,Even after me taking out the Code

XlWrkBk := XlApp.ActiveWorkbook;

Am facing the same ERROR,I do not think that the code that has been removed has anything to do with the Issue am facing.[*-)]

Can you guide me as to what could be going wrong in this case??

Regards,

Prashanth Sanath

4

I can’t see what should be wrong with the code you have written.

Is it the Add Sheet line of code that causes the error, or is it the Create Sheet?
Which version of Excel do you use?

5

It is the AddNewSheet line is the code that is causing the error.The first excel sheet gets created when the normal create book followed by the create sheet is called but the error is thrown at the place were in the new function is called.

Regards,

Prashanth Sanath

6

do you give the user control of excel after the first sheet is created, but before the second one is?
if so then maybe this is the reason?
i must admit i’m just guessing here, as i don’t know for sure what is causing the problem. [:D]
but maybe the automation lets go of the instance when passing control to the user.

7

I’m still new to using automation and the excel buffer table, so this is just a guess, but I have not run across any example where the Excel Buffer table and Excel Automation was used together - it seems like they are two different ways to get the job done. My guess is that the excel automation is erroring out because you are initiating the excel buffer table and then trying to modify the excel document without initializing the automation and opening the excel document.

8

Here is the way that I created multiple sheets with the Excel Buffer.

With ExcelBuf Do Begin
SetUseInfoSheed;
AddInfoColumn (‘Company Name’,False,’’,True,False,False,’’);
AddInfoColumn (CompanyName,False,’’,False,False,False,’’);
ClearNewRow;
NewRow;
AddColumn (‘Sheet’,False,’’,True,False,False,’’);
AddColumn (’#1’,False,’’,True,False,False,’’);
CreateBook;
CreateSheet(’#1’,‘Test Excel’,CompanyName,UserID);
DeleteAll(False);
ClearNewRow;
NewRow;
AddColumn(‘Sheet’,False,’’,True,False,False,’’);
AddColumn(’#2’,False,’’,True,False,False,’’);
CreateSheet(’#2’,‘Test Excel’,CompanyName,UserID);
DeleteAll(False);
GiveUserControl;
END;

Continue with the pattern until you have all the sheets you want, below is a breakdown of what the different attributes do.
AddColumn (,,,,,,);
CreateSheet (,,,);

9

I believe that if the AddSheet-function is created in the excel-buffer table, then it runs on the same instance of the automations.

All you have to do then is to make sure you use the same instance of the excel-buffer in all calls.

At least that’s the way i’ve done it, and that works fine.
Not thereby said it is THE way to do it… [:D]

10

Remember: following line is required if you want to export multiple sheets.

SetUseInfoSheed;
AddInfoColumn (‘Company Name’,False,’’,True,False,False,’’);
AddInfoColumn (CompanyName,False,’’,False,False,False,’’);
ClearNewRow;