Tips&Tricks: Excel Automation

mfabian

I notice that in this forum (and not only ther) the same questions and problems concerning Excel automation are being asked over and over again. So let me share some of my experience with you:

--- DECLARATION ---
Name DataType Subtype Length
xlWorkbooks Automation 'Microsoft Excel 9.0 Object Library'.Workbooks
xlWorksheet Automation 'Microsoft Excel 9.0 Object Library'.Worksheet
xlWorksheetTemp Automation 'Microsoft Excel 9.0 Object Library'.Worksheet
xlApplication Automation 'Microsoft Excel 9.0 Object Library'.Application
xlRange Automation 'Microsoft Excel 9.0 Object Library'.Range
xlWorkbook Automation 'Microsoft Excel 9.0 Object Library'.Workbook
xlBorders Automation 'Microsoft Excel 9.0 Object Library'.Borders
xlHPageBreaks Automation 'Microsoft Excel 9.0 Object Library'.HPageBreaks
xlSheets Automation 'Microsoft Excel 9.0 Object Library'.Sheets

--- OPEN EXCEL ---
CLEAR(xlApplication);
CLEAR(xlRange);
CLEAR(xlWorkbooks);
CLEAR(xlWorkbook);
CLEAR(xlRange);
CLEAR(xlSheets);
CLEAR(xlWorksheet);
CLEAR(xlBorders);

IF CREATE(xlApplication, FALSE) THEN BEGIN // Param FALSE: If excel already started, use existing instance
xlApplication.SheetsInNewWorkbook := 1;
xlApplication.ScreenUpdating(FALSE); //RE01.02


--- note:
{At this point you have two possibilities:
1.Keep Excel visible and update the screen as long as you are developing. This considerably slows down exceution time but you always see what's going on. In case of the crash you know what has been done last and you can close Excel manually}
xlApplication.Visible(TRUE);
xlApplication.ScreenUpdating(TRUE);

{2. If your application is tested, you can turn Excel off while Navision is sending data. This will double the speed of execution. However, if an error occurs, Excel will still be instantiated but will not be visible in the taskbar. Pressing Ctrl-Alt-Del and killing the task will be your only option.
xlApplication.ScreenUpdating(FALSE);
xlApplication.Visible(FALSE);
---

xlWorkbooks := xlApplication.Workbooks;
END
ELSE
ERROR('Could not start Excel');

---

Procedure OpenExistingXlsWorkbook(FName : Text[250],SheetNr : Integer);

xlWorkbooks := xlApplication.Workbooks;
WorksheetAlreadyOpen := FALSE; // this is a local variable
IF xlWorkbooks.Count > 0 THEN BEGIN
ThisWorkbook := xlApplication.ActiveWorkbook;
WorksheetAlreadyOpen := (ThisWorkbook.FullName = FName);
END;
IF NOT WorksheetAlreadyOpen THEN
xlWorkbooks.Open(FName);
xlWorkbook := xlApplication.ActiveWorkbook;
xlSheets := xlWorkbook.Worksheets;
xlWorksheet := xlSheets.Item(SheetNr);

--- note: A preferred method of mine is to use an existing Excel Book as Template where the user can
define Titles, Layout etc. as he wishes. My application then reads this template file, fills in the data and saves the result under a different name. The above code with "WorksheetAlreadyOpen" makes sure that the Template Book is not being opened twice.

---

Procedure XlsNewWorkBook (WName : Text[20]);

xlWorkbooks.Add;
xlWorkbook := xlApplication.ActiveWorkbook;
xlWorksheet := xlApplication.ActiveSheet;
xlWorksheet.Name := Name;


--- SAVE EXCEL WORKSHEET ---
Procedure XlsSaveAs(FName : Text[250]);

IF xlWorkbook.FullName = FName THEN // Same filename
xlWorkbook.Save
ELSE BEGIN
IF FILE.EXISTS(FName) THEN // Forced overwrite, in case the File already exists!
IF ERASE(FName) THEN;
xlWorkbook.SaveAs(FName);
END;

--- CLOSE EXCEL ---
Procedure CloseExcel (Action : Option (Visible,PrintAndQuit,Quit));

Case Action of
Action::Visible: Begin
xlApplication.ScreenUpdating(TRUE);
xlApplication.Visible(TRUE);
End;
Action::PrintAndQuit : Begin
xlApplication.ScreenUpdating(TRUE); //force Recalculation
xlWorkbook.PrintOut;
x

Administrator

This article has been moved to the new forum Tips & Tricks.