mibuso.com
Home› NAV Tips & Tricks
Options

Tips&Tricks: Excel Automation

mfabianmfabian Member Posts: 187
edited 2012-04-17 in NAV Tips & Tricks
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
With best regards from Switzerland

Marcus Fabian
«1

Comments

  • Options
    AdministratorAdministrator Member, Moderator, Administrator Posts: 2,496
    This article has been moved to the new forum Tips & Tricks.
  • Options
    AlexAlex Member Posts: 54
    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
    Best regards
    Alex Schubert

    ERP & Navision

    Dolphin Communication Technologies GmbH
    Otto-Hahnstr. 1 c
    D-69190 Walldorf
  • Options
    mayermayer Member Posts: 50
    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
  • Options
    DavisDavis Member Posts: 8
    And after all don't forget to CLEAR all instancies, or the excell.exe will be still running on the "Background".
  • Options
    GoMaDGoMaD Member Posts: 313
    Another way of checking if a Automation variable is created is:
    IF ISCLEAR(autAutomationVariable) THEN BEGIN
  CREATE(autAutomationVariable);
END;

...

CLEAR(autAutomationVariable);
    Now, let's see what we can see.
    ...
    Everybody on-line.
    ...
    Looking good!
  • Options
    sylvia.choisylvia.choi Member Posts: 3
    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.
  • Options
    JackmeJackme Member Posts: 1
    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?
  • Options
    CharlehCharleh Member Posts: 44
    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.
    Charleh
  • Options
    jegatheeshjegatheesh Member Posts: 10
    8) Thankz. very useful info
  • Options
    Michael_SchumacherMichael_Schumacher Member Posts: 81
    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?
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • Options
    zarrynzarryn Member Posts: 29
    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.
    Zarryn
  • Options
    Michael_SchumacherMichael_Schumacher Member Posts: 81
    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) :?:
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Do you already have access to PartnerSource?

    Otherwise I can mail it to you.
    Marije Brummel | Freelance Business Central Specialist
  • Options
    Michael_SchumacherMichael_Schumacher Member Posts: 81
    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 ;-)
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • Options
    pri_1_ranjanpri_1_ranjan Member Posts: 67
    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
    /PDR
  • Options
    Michael_SchumacherMichael_Schumacher Member Posts: 81
    @pri_1_ranjan:

    You can use the common dialog management to choose Path and filename....
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • Options
    mestremestre Member Posts: 24
    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?
    Cumps,
    Mestre
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • Options
    garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • Options
    vyankuvyanku Member Posts: 791
    Can anybody send me Link of Industry Templet Tool ?
    please send me link.I can access the partner source.
  • Options
    vyankuvyanku Member Posts: 791
    Please send me link of Industry templet tool on my mail id
    v.g.mujumdar@gmail.com
  • Options
    vijay_sr261982vijay_sr261982 Member Posts: 39


    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>
  • Options
    RogerSheetsRogerSheets Member Posts: 15
    great tips .. but im kinda confused ..

    can i put that code in a Nav 4.0SP1 report and have it export it to excel?
  • Options
    SavatageSavatage Member Posts: 7,142
    Yes you can put code on a report & export to excel.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Options
    kirankumarkirankumar Member Posts: 29
    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.
    Hi
  • Options
    Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Try to shorten your Automation variable (for example: XLAppl).
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Options
    kirankumarkirankumar Member Posts: 29
    Hi

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

    Regards
    Kiran.
    Hi
  • Options
    Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Options
    AndwianAndwian Member Posts: 627
    What is this line of code mean? I mean the parameter.
    xlWorkbook.PrintOut(1,10,1,FALSE,txtPrinterName,FALSE,FALSE);

    Thans you in advance.
    Regards,
    Andwian
  • Options
    mkpjsrmkpjsr Member Posts: 587
    very useful....
Sign In or Register to comment.