The following are 1:1 repetitions of my postings from December 2000 to January 2001. son there’s no use to read it if your are a Navision-vet. — 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; xlWorkbook.Close(FALSE); xlApplication.Quit; end; Action::Quit : Begin xlWorkbook.Close(FALSE); xlApplication.Quit; end; ---------------- 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; // 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; Marcus Fabian m.fabian@thenet.ch
I am having problems - I wish to open an Excel template, then modify/insert data from Navision, which I can do at the moment, however, then I wish to save the file using the std. Excel Save As feature, which I can also do, BUT (here comes my difficulty) - I then wish to return the FULL path and filename where the file has been saved to Navision. I am using the Excel automation object to open and write to the Excel file. Any help would be much appreciated. Thanks Ryan
I have used some tips about Automations to get a report out the way i want it… but how do adjust the alignment to right, left and centre? And how do u force the number fields to be with 2 decimals? IF createExcelOutput THEN BEGIN xlsRange.Item(lineCounter, 1, FORMAT(SubtotalText)); xlsRange.Item(lineCounter, 7, FORMAT(BaseWithVAT)); xlsRange.Item(lineCounter, 8, FORMAT(BaseWithoutVAT)); xlsRange.Item(lineCounter, 9, FORMAT(BaseOutside)); xlsRange.Item(lineCounter, 10, FORMAT(Amount)); xlsRange.Item(lineCounter, 11, FORMAT(BaseInvTax0)); xlsRange.Item(lineCounter, 12, FORMAT(BaseInvTax)); xlsRange.Item(lineCounter, 13, FORMAT(“Investmenttax Amount”)); xlsRange.Item(lineCounter, 15, FORMAT(CurrencyAmount)); lineCounter += 1; END; Regards Except these small “problems” i have found Excel Automation as a great way to work around some problems and this time it saved my ass from the horror of telling that the report could not be done [:D] Frode Dahl aimit as
Hello! //Example for a cell type number with 2 decimals (warning!, decimal point is comma in Spain): xlsSheet.Range(‘A1’).NumberFormat := ‘########0.00’; //Example for a cell type text: xlsSheet.Range(‘A2’).NumberFormat := ‘@’; //… and for letter in bold: xlsSheet.Range(‘A2’).Font.Bold := TRUE; Regards, Rafa
xlsSheet.Range(‘A1’).HorizontalAlignment := -4108; //xlCenter xlsSheet.Range(‘A1’).HorizontalAlignment := -4131; //xlLeft xlsSheet.Range(‘A1’).HorizontalAlignment := -4152; //xlRight You can search the “Object Browser” in Excel to find the constants.
quote:
Originally posted by Ryan Whittaker
I then wish to return the FULL path and filename where the file has been saved to Navision.
Ryan, the Workbook.FullName property will do the trick. [8D]
quote:
Originally posted by rafa_marius
Hello! //Example for a cell type number with 2 decimals (warning!, decimal point is comma in Spain): xlsSheet.Range(‘A1’).NumberFormat := ‘########0.00’; Regards, Rafa
When i try this one: xlsRange.Item(lineCounter, 15, FORMAT(CurrencyAmount)); xlsWorksheet.Range(lineCounter, 15).NumberFormat := ‘########0,00’; I get an error who says there was not give an exception. Anybody know what this error is caused by? And can i use (lineCounter, 15) rather then (‘A1’) and so on? Regards Frode Dahl aimit as
Hi all, wouldn’t it be better if normal questions/ answers are posted in the ‘Developer Forum’? It’s only a suggestion [;)]. Andre
quote:
Originally posted by Frodio
xlsWorksheet.Range(lineCounter, 15).NumberFormat := ‘########0,00’; I get an error who says there was not give an exception.
Hello Frode, Unfortunately, the “Range(i,j)” expression is not available and probably the cause of the error message [V] On a similar occasion, I had to resort to the following - ugly but working - construct: // Watch out for German Excel-Version 
// ‘Rot’ instead of ‘Red’, Comma instead of Point Dummy := '#.##0,00_ ;[Rot]-#.##0,00 '; xlsRange1 := xlsWorksheet.Cells.Columns.Item(columnCounter); xlsRange2 := xlsRange1.Rows.Item(lineCounter); xlsRange2.NumberFormat(Dummy); Anybody out there with a more elegant solution? PS: Nice to hear that you got your report up and running [:D]
Hi, You can use the Excel function “ConvertFormula”. Sheet.Range(Excel.ConvertFormula(‘R[1]C[2]’,-4150,1)).NumberFormat := ‘@’; R[0] = Row 1, R[1] = Row 2 and C[2] = Column 3… Function in Navision: ReturnRefC1R1(Row : Integer;Column : Integer) : Text[10] EXIT(Excel.ConvertFormula(‘R[’+FORMAT(Row)+’]C[’+FORMAT(Column)+’]’,-4150,1));
hi I am new to navision can u help me in exporting the report to word and excel , can u give me a sample object using which i can work.
Hi,
could you tell me how to set?
Name DataType Subtype Length
xlWorkbooks Automation ‘Microsoft Excel 9.0 Object Library’.Workbooks
I can’t see Automation data type when I’m going to C/AL globals.
Thanks
Hi Piotr,
Welcome to the Dynamics User Group!
You need to add a variable “xlWorkbooks” and select “Automation” from the DataType option field. Then use the assistedit on the Subtype to select the automation server for Excel. Note Excel/Office needs to be installed on the machine and the version will depend on the version of Excel e.g. Excel 2007 is “Microsoft Excel 12.0 Object Library”. When you select this then a list of classes appear and you can select workbook.
i have a question please.
i want to retrieve all sheet names in a workbook.
i can get only one sheet but i couldnt get the rest.
i tried ObjBook.Sheets.Select(i);
and it doesnt work.
please help
Hello,
I’ve got a question regarding the OP’s solution for excel automation:
IF CREATE(xlApplication, FALSE) THEN BEGIN // Param FALSE: If excel already started, use existing instance
xlApplication.SheetsInNewWorkbook := 1;
xlApplication.ScreenUpdating(FALSE); //RE01.02
This part works fine with MS Excel 2007 but gets me the error with 2010. Does anyone perhaps know what I can do to avoid that? I seem to be unable to solve this on my own… 