mibuso.com

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

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

Okay, that is a fine code ...

... but can i do the same with
Winword ??????



------------------
Best regards
Alex Schubert

ERP & Navision

Dolphin Communication Technologies GmbH
Otto-Hahnstr. 1 c
D-69190 Walldorf
<A HREF="mailto:schubert@dolphinct.de">schubert@dolphinct.de</A>
schubert@dolphinct.de
DolphinCT.de

Example:

CREATE(Winword); // start Word
Winword.Documents.Add; // new blank page
// Winword.Visible(TRUE); // only for testing, because it is very slow

OnAfterGetRecord()
zeile (name);
zeile (Adress);
leerzeile(1);
zeile ("PLZ Code" + ' ' + Ort);

// picture
IF Bild.HASVALUE THEN BEGIN
Bild.EXPORT('c:\'+"Nr."+'.bmp');
Save1:= FALSE; // linkToFile:=False
save2:= TRUE; // SaveWithDocument:=True
Winword.Selection.InlineShapes.AddPicture('c:\'+"Nr."+'.bmp',Save1,save2);
END;

Winword.Selection.InsertBreak; // new page

OnPostDataItem()
Winword.Visible(TRUE); // Winword visible
Winword.Activate; // Winword aktiv

zeile(text : Text[250])
Winword.Selection.TypeText(text); // Zeile schreiben
leerzeile(1);

leerzeile(Anzahl : Integer)
FOR i := 1 TO Anzahl DO
Winword.Selection.TypeParagraph; // space-lines

And after all don't forget to CLEAR all instancies, or the excell.exe will be still running on the "Background".

Another way of checking if a Automation variable is created is:

Hi there,

I have created a program to export to excel using excel buffer. Now the client wants me to add a logo to the document header.

How can I keep my original report while adding a logo to the header?

I've tried :
XlWorkSheet.PageSetup.LeftHeaderPicture.Filename := 'C:\picture.BMP'
which didn't work..

Then
XlWorkSheet.Pictures.Insert('C:\picture.BMP')
which couldn't compile...

Only succeeded with
XlWorkSheet.SetBackgroundPicture('C:\picture.BMP');
which is not what I want!

I'm very new to all this automation programming. Pls give a hint or two.

Thanks in advance.

I dont use the Excel Buffer, but the code that works for me is:

Book:=Excel.Workbooks.Add(-4167);
Sheet:=Excel.ActiveSheet;
xlWorksheet.PageSetup.CenterHeaderPicture.Filename := 'C:\Header.bmp';
Sheet.PageSetup.CenterHeaderPicture.Height := 197;
Sheet.PageSetup.CenterHeaderPicture.Width := 418;
Sheet.PageSetup.CenterHeaderPicture.Brightness := 0.36;
Sheet.PageSetup.CenterHeaderPicture.ColorType := 1;
Sheet.PageSetup.CenterHeaderPicture.Contrast := 0.39;
Sheet.PageSetup.CenterHeaderPicture.CropBottom := 0;
Sheet.PageSetup.CenterHeaderPicture.CropLeft := 0;
Sheet.PageSetup.CenterHeaderPicture.CropRight := 0;
Sheet.PageSetup.CenterHeaderPicture.CropTop := 0;
Sheet.PageSetup.CenterHeader := '&G';

Note: You will have to press Print Preview to view the logo.

PS: Anyone knows how to insert a row into Excel at a given place?

Selection.EntireRow.Insert

I think the selection object is part of the application object but I'm not sure, try AppVariable.Selection.EntireRow.Insert or SheetVariable.Selection.EntireRow.Insert. This only inserts a blank row mind, so you will have to put your data into this row after inserting it. You will have to move the cursor to the desired row first too.

Thankz. very useful info

This is nice work to put data out to Excel or Word.
But what about the other way around?

Any Hints and tricks to get Data out of excel into Navision by C/AL code?

Take a look at the Industry Template tool in 3.70B. It has a utility that will take data from Excel and insert in Navision. The key pieces are Table 8601 Mapping Header, Table 8602 Mapping Lines and Codeunit 8601 Mapping Handle. Hope this helps.

where can I get this from?
we only have 4.00 since we are a very new partner (not yet completely certified)

Do you already have access to PartnerSource?

Otherwise I can mail it to you.

not completely, the download rights are not given.....

ok I'll send you an email, so that you can answer to that address