Information sheet not printed at the right position in Excel Export

FragenFrager

Hello,
when creating an Excel export I have the following, annoying problem: A report with a sheet for the data and a sheet for the information is to be created as cover sheet. Basically, this works, but unfortunately the cover sheet is not printed to the left of the sheet with the data, instead it ist printed to the right (as second sheet).

Here is the code to control the whole.

OnPostReport ()
IF gboExportToExcel THEN BEGIN
  Make Excel Info;
  ExcelBuf.SetUseInfoSheet;
  ExcelBuf.CreateBook ( '', 'test');
  ExcelBuf.WriteSheet ( 'test', COMPANY NAME, USERID);
  ExcelBuf.CloseBook;
  ExcelBuf.SetFriendlyFilename ( 'test');
  ExcelBuf.OpenExcel;
  ExcelBuf.GiveUserControl;
END;

The data header is generated in the OnAfterGetRecord trigger of the controlling DataItem.
I have made a few attempts to change the order of the output, without success, either it does not come out at all, or the sheets are output in the order described. Anyone an idea?

thomasbarbut

Hello,
check this post:
https://community.dynamics.com/nav/b/navreallife/archive/2016/04/16/my-version-of-multiple-excel-sheets-using-excel-buffer

FragenFrager

Hello,
thanks for the immediate answer. I allready went through the mentioned post, but is it really necessary to manipulate the Excel Buffer? I thought, using an information sheet is standard technology, but it does not work in nav 2017 the normal way anymore?

thomasbarbut

Hello,

I had the same issue with 1 report. I just left it there on the right side and no one complained.
Maybe you could move this section from WriteSheet to CreateBook function at the beginning in table 370.
Though I am not sure what effects it will have. Just test it.

IF UseInfoSheet THEN
IF TempInfoExcelBuf.FINDSET THEN BEGIN
XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(Text023);
REPEAT
InfoRecNo := InfoRecNo + 1;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt,LastUpdate,RecNo + InfoRecNo,TotalRecNo) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
IF TempInfoExcelBuf.Formula = '' THEN
WriteCellValue(TempInfoExcelBuf)
ELSE
WriteCellFormula(TempInfoExcelBuf)
UNTIL TempInfoExcelBuf.NEXT = 0;
END;

best regards,
Thomas Barbut