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.

Alex

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

mayer

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

Davis

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

GoMaD

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

IF ISCLEAR(autAutomationVariable) THEN BEGIN
  CREATE(autAutomationVariable);
END;

...

CLEAR(autAutomationVariable);

sylvia.choi

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.

Jackme

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?

Charleh

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.

jegatheesh

8) Thankz. very useful info

Michael_Schumacher

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?

zarryn

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.

Michael_Schumacher

zarryn wrote:

Take a look at the Industry Template tool in 3.70B.

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

Marije_Brummel

Do you already have access to PartnerSource?

Otherwise I can mail it to you.

Michael_Schumacher

Mark Brummel wrote:

Do you already have access to PartnerSource?

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

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

pri_1_ranjan

hey what about setting of the file path and then proceeding ..
here for saving purpose u have shown the way to save the file and to overwrite the existing one if either that one exits .. what if it is required to be stored at some specified path

Michael_Schumacher

@pri_1_ranjan:

You can use the common dialog management to choose Path and filename....

mestre

fabian wrote:

--- 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

Hi fabian,
can you finish this example?

Marije_Brummel

I don't think fabian is posting anymore, since his last post is of 2001.

You can have a look at Table 370 for excel automation examples or ask a question about the specific command you need.

garak

There is an easy way to find out which Classes you must add as variable to get the positive result.

Start our M$ Office Application. For example Word. Then starts the Macrofunction. Then do that what Navision should be do. When you finished, stop the Macrorecording. Then take a look into the VBA-Code. Here you can see the results an the "Automations" do you need.

Also take a look into the Office Outomation Strukture (The Struckture Tree).

So you can finish you Navisioncode faster (its my experience.

Regards

vyanku

Can anybody send me Link of Industry Templet Tool ?
please send me link.I can access the partner source.

vyanku

Please send me link of Industry templet tool on my mail id
v.g.mujumdar@gmail.com

vijay_sr261982

Thanx for giving Valuble info Fabin. Currently i got Excel Report requirement from my Client. u Info will greatly help me in developing the report.

Thanx once again for giving a valuble Tips =D>

RogerSheets

great tips .. but im kinda confused ..

can i put that code in a Nav 4.0SP1 report and have it export it to excel?

Savatage

Yes you can put code on a report & export to excel.

kirankumar

Hi

Nice to meet you all.
I tried the code you have given for XL-Automation But at the Compilation
time it throws an Error like:
'The Variable XLAPPLICATION WORKBOOK::PIVOTTABLE is defined more
than once'.

I don't know what's the reason for this is..Could you Pls give me the Solution to resolve

Thank You
kiran.

Luc_VanDyck

Try to shorten your Automation variable (for example: XLAppl).

kirankumar

Hi

That's great. Its Compiled..But Could U please Explain How to Use this ..?

Regards
Kiran.

Luc_VanDyck

If you don't know how to use it, why do you create that variable in the first place? I never worked with this PIVOTTABLE-thing, so I'm afraid I can't help you out here.

Andwian

What is this line of code mean? I mean the parameter.

xlWorkbook.PrintOut(1,10,1,FALSE,txtPrinterName,FALSE,FALSE);

Thans you in advance.

mkpjsr

very useful....