EXcel Buffer Already Exists Error

haryramani

Am in process of exporting report 722 Phy Inventory List
I have around 67 pages... My report is exporting till 20th page and then stops with an error as
"Excel buffer already exists,Row no:-1,Column No:-1"

Coding s as follows

Item Journal line-OnPredataItem()
IF PrintToExcel THEN BEGIN
RowNo := 1;
EnterCell(RowNo,1,'Item No.',TRUE,FALSE,FALSE);
EnterCell(RowNo,2,'Description',TRUE,FALSE,FALSE);
EnterCell(RowNo,3,'Vendor Item No.',TRUE,FALSE,FALSE);
EnterCell(RowNo,4,'Location Code',TRUE,FALSE,FALSE);
EnterCell(RowNo,5,'Qty. in NAV',TRUE,FALSE,FALSE);
EnterCell(RowNo,6,'Qty.-Phys. Inventory',TRUE,FALSE,FALSE);
END;


Item Journal line-OnAftergetRecord()
IF PrintToExcel THEN BEGIN
RowNo := RowNo + 1;
EnterCell(RowNo,1,"Item No.",FALSE,FALSE,FALSE);
EnterCell(RowNo,2,Description, FALSE,FALSE,FALSE);
EnterCell(RowNo,3,Item."Vendor Item No.",FALSE,FALSE,FALSE);
EnterCell(RowNo,4,"Location Code",FALSE,FALSE,FALSE);
EnterCell(RowNo,5,FORMAT("Qty. (Calculated)"),FALSE,FALSE,FALSE);
END;


item journal line-postdataitem()
IF PrintToExcel THEN BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet('Inventory','Inventory',COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;
END;


Entercell()

TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE("Row No.",RowNo);
TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
TempExcelBuffer."Cell Value as Text" := CellValue;
TempExcelBuffer.Formula := '';
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := Underline;
TempExcelBuffer.INSERT;




i have set recvariable as temprory,if i set Debugger->activate..
it is stopping in TempExcelbuffer.INSERT
Can sum1 tel how 2 solve dis error??

lavanyaballurgi

the problem is with your row increment. your debugger is stopping at INSERT because it is finding that already there is data present in ROW=1, COL=1 so INSERT will not be executed.
Check how & where have you incremented your Row No.

sunnyk

Put RowNo := RowNo + 1; at the end of every section (inside Begin End) instead of at the beginning.

Item Journal line-OnPredataItem()
IF PrintToExcel THEN BEGIN

EnterCell(RowNo,1,'Item No.',TRUE,FALSE,FALSE);
EnterCell(RowNo,2,'Description',TRUE,FALSE,FALSE);
EnterCell(RowNo,3,'Vendor Item No.',TRUE,FALSE,FALSE);
EnterCell(RowNo,4,'Location Code',TRUE,FALSE,FALSE);
EnterCell(RowNo,5,'Qty. in NAV',TRUE,FALSE,FALSE);
EnterCell(RowNo,6,'Qty.-Phys. Inventory',TRUE,FALSE,FALSE);
RowNo := RowNo + 1
END;


Item Journal line-OnAftergetRecord()
IF PrintToExcel THEN BEGIN
EnterCell(RowNo,1,"Item No.",FALSE,FALSE,FALSE);
EnterCell(RowNo,2,Description, FALSE,FALSE,FALSE);
EnterCell(RowNo,3,Item."Vendor Item No.",FALSE,FALSE,FALSE);
EnterCell(RowNo,4,"Location Code",FALSE,FALSE,FALSE);
EnterCell(RowNo,5,FORMAT("Qty. (Calculated)"),FALSE,FALSE,FALSE);
RowNo := RowNo + 1;
END;

And put RowNo := 1; @ Onprereport

Chinmoy

Hi haryramani,

I did not find any problem in the code you have shown us. Instead, I suspect two points you have mentioned, one is the Temporary nature of the recvariable (can you check after making the Temporary property FALSE) and the other is RowNo := 1; (is this statement somehow getting fired from somewhere?).

Good luck!

postsaurav

Hi haryramani,

Can you try these steps -

1) Run Table Excel Buffer and delete all rows.
2) Keep the TempExcelBuffer as Temporary. (Check Twice)
3) Replace code as given below, this is the other way for export to excel.

Item Journal line-OnPredataItem()

IF PrintToExcel THEN BEGIN
  TempExcelBuffer.NewRow;
  TempExcelBuffer.AddColumn('Item No.',FALSE,'',TRUE,FALSE,TRUE,'');
  TempExcelBuffer.AddColumn('Description',FALSE,'',TRUE,FALSE,TRUE,'');
  TempExcelBuffer.AddColumn('Vendor Item No.',FALSE,'',TRUE,FALSE,TRUE,'');
  TempExcelBuffer.AddColumn('Location Code',FALSE,'',TRUE,FALSE,TRUE,'');
  TempExcelBuffer.AddColumn('Qty.In NAV',FALSE,'',TRUE,FALSE,TRUE,'');
  TempExcelBuffer.AddColumn('Qty.-Phys. Inventory',FALSE,'',TRUE,FALSE,TRUE,'');
END;


Item Journal line-OnAftergetRecord()
IF PrintToExcel THEN BEGIN
  TempExcelBuffer.NewRow;
  TempExcelBuffer.AddColumn("Item No.",FALSE,'',FALSE,FALSE,FALSE,'');
  TempExcelBuffer.AddColumn(Description,FALSE,'',FALSE,FALSE,FALSE,'');
  TempExcelBuffer.AddColumn(Item."Vendor Item No.",FALSE,'',FALSE,FALSE,FALSE,'');
  TempExcelBuffer.AddColumn("Location Code",FALSE,'',FALSE,FALSE,FALSE,'');
  TempExcelBuffer.AddColumn(FORMAT("Qty. (Calculated)"),FALSE,'',FALSE,FALSE,FALSE,'');
  TempExcelBuffer.AddColumn('',FALSE,'',FALSE,FALSE,FALSE,'');
END;


Report - OnPostReport()  [b]//CODE LOCATION CHANGED[/b]

IF PrintToExcel THEN BEGIN
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet('Inventory','Inventory',COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;
ERROR('');
END;

Best of Luck

pberb

If you are getting the error, "The Excel Buffer already exists. Identification fields and values: Row No.='1',Column No.='1'" and you've already checked the other answers here, ensure in C/AL on all of your report OnPreSections you have:

CurrReport.SHOWOUTPUT := NOT PrintToExcel;
or
CurrReport.SHOWOUTPUT(NOT PrintToExcel);
(whichever you prefer)

Although lacking this statement usually doesn't cause an issue, under rare circumstances it can cause this error. So ensure you always have it when exporting to excel!

By the way, why are you developing exports to excel when it's built into NAV 2013, and there are add-ons like http://www.print2excel.com for older versions of NAV? (no I'm not affiliated with print2excel)