Closing / Terminating Excel

rkaufmannrkaufmann Member Posts: 71
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

Comments

  • lakshmivallurulakshmivalluru Member Posts: 168
    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
    LR
  • rkaufmannrkaufmann Member Posts: 71
    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.
  • MymoonMymoon Member Posts: 6
    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;
  • rkaufmannrkaufmann Member Posts: 71
    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.
  • tvandongentvandongen Member Posts: 78
    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	
    
Sign In or Register to comment.