Update existing Excel file through Excel buffer?

Tiwaz

Hi guys. I have excel file and I want to fill excell buffer temp. through processing only report and then to fill my excel table from there.
This is what I have done.
I created ProcessingOnly report where I created this function:


LOCAL EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean;Format : Text[30];CellType : Option)
ExcelBuffer.INIT;
ExcelBuffer.VALIDATE("Row No.",RowNo);
ExcelBuffer.VALIDATE("Column No.",ColumnNo);
ExcelBuffer."Cell Value as Text" := CellValue;
ExcelBuffer.Formula := '';
ExcelBuffer.Bold := Bold;
ExcelBuffer.Italic := Italic;
ExcelBuffer.Underline := UnderLine;
ExcelBuffer.NumberFormat := Format;
ExcelBuffer."Cell Type" := CellType;
ExcelBuffer.INSERT;

Then in OnAfterGetRecord I placed this


Travel Order - OnAfterGetRecord()
FileName := 'D:\Putni nalog.xlsx';
SheetName := 'Sheet1';
ExcelBuffer.OpenBook(FileName,SheetName);
EnterCell(2,2,'TravelOrder',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
//ExcelBuffer.CreateBookAndOpenExcel('Sheet1','Report',COMPANYNAME,USERID);
//ExcelBuffer.OverwriteAndOpenExistingExcel(FileName);

I want to write on my file Putni nalog.xlsx using
EnterCell(2,2...);
EnterCell(2,5...); etc, depending what I need and in which field in my file do I want to write it.
But when I try using OverwriteAndOpenExistingExcel it gives me error 'The file does not exist'.
What should I use to write on my existing file?
And how can I write some value from my table "Travel Order" since
EnterCell(2,2,TravelOrder."No",FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text); for example, simply does not work?

THANK YOU!

jwilder

Not sure if I am totally following you but here are a few suggestions:
1) I like the function you created called EnterCell, keep this.

2) From the OnAfterGetRecord insert each cell of data using the EnterCell function. So you will have one line of code for each cell you are inserting:
EnterCell(2,1,TravelOrder."No.",FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
EnterCell(2,2,TravelOrder.Description,FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);

3) I think you need to upload your file to the service tier so you can work on it there as well. Then the OverwriteAndOpenExistingExcel will download this tempfile back to your original file. One way to do this is:
TempFileName := FileMgt.UploadFileSilent(FileName); //FileMgt is Codeunit 419

4) I would do your file OpenBook on the OnPostReport trigger. Also insert your headings here as well (typically RowNo 1 of the spreadhseet. I would have something like this here:
EnterCell(1,1,'Travel Order No.',True,False,True,ExcelBuffer."Cell Type"::Text);
EnterCell(1,2,'Travel Order Description',True,False,True,ExcelBuffer."Cell Type"::Text);
ExcelBuffer.UpdateBook(TempFileName,'Your Sheet Name Here'); //Temp FileName
ExcelBuffer.WriteSheet('Report Header Here',COMPANYNAME,USERID);
ExcelBuffer.CloseBook;
ExcelBuffer.OverwriteAndOpenExistingExcel(FileName); //Original file name \Putni nalog.xlsx
ExcelBuffer.GiveUserControl;

See if this works. Normally you select a sheet before updating it so respond if that is a requirement.

jwilder

Not sure if I am totally following you but here are a few suggestions:
1) I like the function you created called EnterCell, keep this.

2) From the OnAfterGetRecord insert each cell of data using the EnterCell function. So you will have one line of code for each cell you are inserting:
EnterCell(2,1,TravelOrder."No.",FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
EnterCell(2,2,TravelOrder.Description,FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);

3) I think you need to upload your file to the service tier so you can work on it there as well. Then the OverwriteAndOpenExistingExcel will download this tempfile back to your original file. One way to do this is:
TempFileName := FileMgt.UploadFileSilent(FileName); //FileMgt is Codeunit 419

4) I would do your file OpenBook on the OnPostReport trigger. Also insert your headings here as well (typically RowNo 1 of the spreadhseet. I would have something like this here:
EnterCell(1,1,'Travel Order No.',True,False,True,ExcelBuffer."Cell Type"::Text);
EnterCell(1,2,'Travel Order Description',True,False,True,ExcelBuffer."Cell Type"::Text);
ExcelBuffer.UpdateBook(TempFileName,'Your Sheet Name Here'); //Temp FileName
ExcelBuffer.WriteSheet('Report Header Here',COMPANYNAME,USERID);
ExcelBuffer.CloseBook;
ExcelBuffer.OverwriteAndOpenExistingExcel(FileName); //Original file name \Putni nalog.xlsx
ExcelBuffer.GiveUserControl;

See if this works. Normally you select a sheet before updating it so respond if that is a requirement.

Tiwaz

YOU ARE A LIFESAVER!

eazelcom

@Tiwaz any help with your code

Tiwaz

eazelcom wrote: »

@Tiwaz any help with your code

This was years ago I believe that I pretty much solved it