PROCEDURE EV_SaveExcel@1160180002(FilePath@1160180001 : Text); VAR FileNameClient@1160180000 : Text; BEGIN CloseBook; IF OpenUsingDocumentService('') THEN EXIT; IF NOT PreOpenExcel THEN EXIT; FileNameClient := FileManagement.DownloadTempFile(FileNameServer); FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,GetFriendlyFilename,FilePath); XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient); PostOpenExcel; XlHelper.CallSaveAs(XlWrkBk,FileNameClient) CloseBook; END;
Answers
...create your own function with the above code and then call it before saving the workbook.
XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient);
But I don't want to open the Excel-book but just saving it.
PROCEDURE CreateBookOnly@1160180001(SheetName@1160180003 : Text[250];ReportHeader@1160180002 : Text[80];CompanyName@1160180001 : Text[30];UserID2@1160180000 : Text;BookCreated@1160180004 : Boolean);
BEGIN
ExcelBookCreated := BookCreated;
NewSheetName := SheetName;
IF NOT BookCreated THEN
CreateBook(SheetName);
CurrentRow := 0;
CurrentCol := 0;
WriteSheet(ReportHeader, CompanyName, UserID2);
END;
Where 'ExcelBookCreated' and 'NewSheetName' are global variables. In function 'Writesheet' I've added this code:
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNT + InfoExcelBuf.COUNT;
RecNo := 0;
//Code added >>
IF ExcelBookCreated THEN
IF ActiveSheetName <> NewSheetName THEN
XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(NewSheetName);
//Code added <<
XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape);
For witting the excel-book I fill the excel-buffer for every sheet, call my new function 'CreateBookOnly' and delete the buffer (important!). When finished writing the excel-book I call a new custom-function on table 370:
PROCEDURE EV_SaveExcel@1160180002(FilePath@1160180001 : Text);
VAR
FileNameClient@1160180000 : Text;
BEGIN
CloseBook;
IF OpenUsingDocumentService('') THEN
EXIT;
IF NOT PreOpenExcel THEN
EXIT;
FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
FileManagement.MoveAndRenameClientFile(FileNameClient,GetFriendlyFilename,FilePath);
END;
It seems to be working. To prevent being prompted for overwriting the file, I'm working on TEMP-files and uses only XlHelper.CallSaveAs to finalize the file. My code ended up like this:
CloseBook;
IF OpenUsingDocumentService('') THEN
EXIT;
IF NOT PreOpenExcel THEN
EXIT;
FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClient);
PostOpenExcel;
FileNameClient_OpenedAndAutofitted := FileManagement.ClientTempFileName('xlsx');
FileManagement.CopyClientFile(FileNameClient,FileNameClient_OpenedAndAutofitted,TRUE);
FileNameClient_OpenedAndAutofitted := FileManagement.MoveAndRenameClientFile(FileNameClient_OpenedAndAutofitted,GetFriendlyFilename,FilePath);
FileManagement.DeleteClientFile(FileNameClient_OpenedAndAutofitted);
XlHelper.CallSaveAs(XlWrkBk,FileNameClient_OpenedAndAutofitted);
QuitExcel;
I,ve experienced problems with 'Excel' "holding" on to the file, but I think/hope I've solved it by calling the function 'QuitExcel' at the end:-)
MANY THX for helping me out!