I notice that in this forum (and not only there) 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; — 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; xlWorkbook.Close(FALSE); xlApplication.Quit; end; Action::Quit : Begin xlWorkbook.Close(FALSE); xlApplication.Quit; end; ------- By the way: I am working as independend self-employed Navision programmer (mostly as trouble-shooter) for any (=worldwide) Solution center which might need me (and is able to pay me 
). So if feel you could need my help, drop me a note. Admin: please forgive me it I missuse this forum to advertise myselves but as long as the “Navision Job Offers” is not functional … Marcus Fabian ---------------------------------- fabian@ddag.ch m.fabian@thenet.ch 079 439 78 72 ----------------------------------
This should save me some time for my Automation project … Thanks for sharing Marcus!
Legend
Let’s get rock …with Excel… 
Adding a new workbook using a template or blank var – Template : text[250]; // The template file name – TestFile : File; // For knowing if the template file is – TestResult : Boolean; // to store result – xlApp : Excel Automation Application = Marcus’ xlApplication – xlWorkBook : Excel Automation workbook — TestResult := FALSE; — IF (STRLEN(Template) >0) THEN — BEGIN ----- IF (TestFile.OPEN(Template)) THEN ----- BEGIN ------- // We have the file there… so we can use it ------- TestFile.CLOSE; ------- TestResult := TRUE; ----- END; — END; — IF (TestResult) THEN — BEGIN ----- xlWorkBook := xlApp.Workbooks.Add(Template) — END — ELSE — BEGIN ----- // If there is no filename or there is no file… we ----- // should create a blank one. ----- xlWorkBook := xlApp.Workbooks.Add (-4167); — END; ------------- Setting a value at a specified position: --------------- var – _Col : Text; – _Row : Number; – NewValue : our value – xlworkSheet : Excel Automation Worksheet done initializated first – xlWorkSheet.RANGE(_Col+FORMAT(_Row)).value := NewValue; ----- Adding a chart to a worksheet var – xlChart - Excel automation xlchart – xlChartObjects - Excel automation xlchartobjects – xlChartObject - Excel automation xlchartobject – xlRange - Excel automation xlrange – xlWorkSheet - Excel automation xlworksheet – _startcol : text - col where starts data – _startrow : integer - row where starts data – _endcol : text — can u guess?? – _endrow : integer - no need to explai… do i? – _xpos : x coordinate where the chart will start showing – _ypos : y coordinate where the chart will start showing – _width : integer - width of the chart – _height: integuer - u can guess… – xlRange := xlWorkSheet.Range(_StartCol+FORMAT(_StartRow)+’:’+_EndCol+FORMAT(_EndRow)); – xlChartObjects := xlWorkSheet.ChartObjects; – xlChartObject := xlChartObjects.Add(_xpos, _ypos, _width, _height); – xlChartObject.Chart.ChartWizard (xlRange,_gallery,_format,_plotby,_categorylabels,_serieslabels,_HasLegend, _Title, _CategoryTitle, _ValueTitle, _Extratitle); The values of _gallery, _format, _plotby, _categorylabels… are shown in the excel programmers help file (are the same than when using from VBA). (It could be interesting that people in Navision HQ takes all this info and re-writes their poorly helping programmers manual of Navision Financials… it would really help all us). – Alfonso Pertierra apertierra@teleline.es Spain
Allow me, to add two notes to your post: 1) Providing you use the procedures above the decision whether to open an existing or create a new Workbook is quite simple: IF FILE.EXISTS(OpenExcelFileName) THEN BEGIN OpenExistingXlsWorkbook(OpenExcelFileName,WorkbookNr); END ELSE BEGIN NewXlsWorkbook(Templatename); END; 2) Setting the value of a cell like … xlWorkSheet.RANGE(_Col+FORMAT(_Row)).value := NewValue … does not work with all excel versions. Usually the worksheets are displayed in the form A1,E6 rather than 1,1 and 5,6 so you would have to convert a Col-Number into a letter (such as 5 → E or 28 → AB) Of course the best is to create functions for that: // Convert Col Index to Letter: 5->E, 28->AC Function xlsCol (col : Integer) : Text10; begin IF Col > 26 THEN BEGIN ColFirst := Col DIV 26; Col := Col MOD 26; END ELSE ColFirst := 0; Letters := ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’; IF ColFirst <> 0 THEN EXIT (STRSUBSTNO(’%1%2’,Letters[ColFirst],Letters[col])) ELSE EXIT (STRSUBSTNO(’%1’,Letters[Col])); end; Function xlsCell (Col, row : Integer) : Text15; begin EXIT (STRSUBSTNO(’%1%2’,xlsCol(col),row)); end; // Put Decimal value in a cell Function xlsDecimal(col, row : Integer; _Value : Decimal); begin xlRange := xlWorksheet.Range(xlsCell(col,row)); xlRange.Value := _value; end; Anyone interested in how to set ColWidth, rowHeigh, Fonts, FontSizes, Range-Names, formulas and other stuff? (Today it’s too late). Marcus P.S.: Interesting, it was so long ago I did the original post and so long someone noticing it Must be a new hobby of Navision programmers to get into Excel automation. Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch
LOL… i didn’t noticed that your message was the same posted in July… LOL (now i understand why it sounds me u’were telling something that sounds similar to something i’d read some time ago…). BTW… thnx for your comments (i think that the numbers for cols is a better way for working with excel automation… as using letters gives you some headaches when having to control lots of them… and having them starting as AA,AB… etc and u didn’t though first on it… – Alfonso Pertierra apertierra@teleline.es Spain
Sure Col-Numbers are better for us (Developers) but most users are used to these stupid letters and they are the one to pay for our bread Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch
Maybe I missed or not, but my question is how would change to another sheet within the workbook? For Progress = 1 to 100 if Progress = 100 then Progress = 0 Else Next Progress
Var Excel = Microsoft Excel 9.0 Object Library.Application Var Sheet = Microsoft Excel 9.0 Object Library.Worksheet Sheet := Excel.Worksheets.Item(‘Sheetname’);
THANK YOU VERY MUCH!!! That is exactly what I needed. For Progress = 1 to 100 if Progress = 100 then Progress = 0 Else Next Progress
Moved from “Attain/Financials Developer Forum” to “Attain/Financials Developer FAQ”.