Closing / Terminating Excel

rkaufmann

Hi,

I have to do the following job:
1. Open an existing Excel-File (used as template)
2. Add data to the Excel-File
3. save as new file (other filename)
4. close the new file
5. create an email an attaching the new file.

All of the above is not problem, except of #4
I do not manage to close the file an to terminate the Excel-task.

Here my code:

//
//
//

CLEAR(Excel);
IF NOT CREATE(Excel) THEN
ERROR('Excel could not be found.');

Excel.Workbooks._Open(GLSetup."Excel-File");
Book := Excel.ActiveWorkbook;
Sheet := Book.Worksheets.Item(GLSetup."Excel-Sheet");

...
Code to add data
...

Book.SaveCopyAs(GLSetup."Excel-NewFilename");
Book.Close(FALSE);
Excel.Quit;
CLEAR(Excel);

//
//
//

"Excel" = Automation.Application
"Book" = Automation.Workbook
"Sheet" = Automation.Worksheet

The result of my programming is:
The additional data is added to the Excel-File.
The File is saved with a new name.
The Workbook is closed.
The Excel-Task is still running an locking the new file.
The Excel-Task is only visible in Taskmanager, even if I make it visible before closing the workbook an quiting excel, by useing "Excel.Visible(TRUE);"

Does anyone know how to quit / close 7 terminate Excel so the new file is not locked by the Excel-task anymore?

Thankx,

Rolf

lakshmivalluru

I think u command will close the GLSetup."Excel-File". I think u need to check if GLSetup."Excel-NewFilename" is open then close it. try it

rkaufmann

That could be the trick, but how can I check, if the new file is still open?
And if it is open, how can I close it?

Any hint is welcome.

Mymoon

Hello,
i had the same problem, i dont know how i fixed it but now i am using the following code and dont get any error.
Good luck...

(but I created a new workbook)

BEGIN
CLEAR(Excel);
CREATE(Excel,FALSE);
Book := Excel.Workbooks.Add(-4167);
Sheet := Excel.ActiveSheet;
Sheet.Name := 'blabla';

//add data to excel sheet

Sheet._SaveAs(FileName);
Excel.Visible(TRUE);
END;

rkaufmann

Thanks four your reply,

but your code "only" saves the modified sheet as a new file and than gives the user control over it.

My problem is, that the sheet must be saved under a new name and than the whole Excel-Application including any open files must be closed, so I can get control over the new file and attach it to an email.

tvandongen

We are doing a similar part, except we don't mail the sheet.

Used Code:
IF (Filename <> '') THEN
BEGIN
  IF (EXISTS(Filename)) THEN
  BEGIN
        // Open de spreadsheet in Excel.

    CLEAR(ExcelCU);

    ExcelCU.InitExcel();
    ExcelCU.OpenWorkbook(Filename);
    // Add values

    ExcelCU.WriteCell(1,  7, "Input 1");
    ExcelCU.WriteCell(1,  8, "Input 2");
    ExcelCU.WriteCell(1,  9, "Input 3");
    ExcelCU.SaveExcelWorksheet(Filename);
    ExcelCU.StartMacro('Macro1');  // this macro is used to print another sheet 
    YIELD; // give the macro time to run
    ExcelCU.TermExcel;
    CLEAR(ExcelCU);
  END;
END;

Parameters
ExcelCU	Codeunit	Excel Automation	

Code code-unit excel automation

Function InitExcel()
CREATE(Excel);


Function OpenWorkbook(Name : Text[250])
LastSheetNo := 1;
CurrentSheetNo := LastSheetNo;
Book := Excel.Workbooks.Open(Name);
Sheet[LastSheetNo] := Excel.ActiveSheet();


Function WriteCell(Col : Integer;Row : Integer;Contents : Text[250])
CurRange := ConvertHorizontal(Col)+FORMAT(Row);
Sheet[CurrentSheetNo].Range(CurRange).Value := Contents;
IF CurBoldSetting <> -1 THEN
  Sheet[CurrentSheetNo].Range(CurRange).Font.Bold := CurBoldSetting;
IF CurUnderlineSetting <> -1 THEN
  Sheet[CurrentSheetNo].Range(CurRange).Font.Underline := CurUnderlineSetting;
IF CurItalicSetting <> -1 THEN
  Sheet[CurrentSheetNo].Range(CurRange).Font.Italic := CurItalicSetting;
IF CurFontName <> '' THEN
  Sheet[CurrentSheetNo].Range(CurRange).Font.Name := CurFontName;

IF CurForegroundColour <> -1 THEN BEGIN
  Sheet[CurrentSheetNo].Range(CurRange).Font.ColorIndex := CurForegroundColour;
END;
IF CurBackgroundColour <> -1 THEN BEGIN
  Sheet[CurrentSheetNo].Range(CurRange).Interior.ColorIndex := CurBackgroundColour;
  Sheet[CurrentSheetNo].Range(CurRange).Interior.Pattern := 1;
END;

PosCol := Col;
PosRow := Row;


Function SaveExcelWorksheet(Name : Text[250])
Book.Save;


Function StartMacro(MacroNaam : Text[30])
{ StartMacro }

Excel.Run(MacroNaam);
YIELD;
Sheet[LastSheetNo] := Excel.ActiveSheet();



Function TermExcel()
Excel.Quit();
CLEAR(Excel);

Parameters Code-unit:

Name	DataType	Subtype	Length
Excel	Automation	'Microsoft Excel 9.0 Object Library'.Application	
Book	Automation	'Microsoft Excel 9.0 Object Library'.Workbook	
Sheet	Automation	'Microsoft Excel 9.0 Object Library'.Worksheet	
xlSheet	Automation	'Microsoft Excel 9.0 Object Library'.Worksheets	
Chart	Automation	'Microsoft Excel 9.0 Object Library'.Chart