Hi, We are looking to change the default font for an Excel automated report, i.e. the entire document will need to change to this font (Arial,8 size). I have been through all the threads to see if anybody has managed this, but not had much look. The best solution so far seems to be the template solution, which isn’t really going to help us here… Anybody able to offer help? I presume we would need to change the normal style in the file, but how to control this through Excel automation? Many thanks Meint
Depending on how you want to further deal with the resulting file, I would suggest the easy solution of simply selecting all cells beforehand and setting the desired font. This will do the trick: xlsRange := xlsWorksheet.Cells; xlsRange.Font.Name := 'Zapf Dingbats'; xlsWorksheet, and xlsRange are of course C/AL automation variables of the appropriate subtype [;)]. This is equivalent to selecting all cells in the worksheet and setting their font. However, this will not change an underlying template, style sheet or whatever. So, when you later continue working with the sheet, you might not get the expected results (inserting new lines will retain the modified format, though).
Heinz, Thanks for your reply, we looked at doing this and as you stated, it isn’t quite as elegant as what we were looking for. The problem is that these files will be sent to customers and therefore need to look pretty professional. Any more takers?, somebody really needs to write a book on Navision and Excel integration! Thanks Meint
Well, in principle, everything you can do in the Excel application, you can do with automation from C/AL. You can modify the workbook’s style with e.g. (in VB) ActiveWorkbook.Styles(“Normal”).Font.Name = “Bodoni PosterCompressed”. This will make all cells as well as row and column headers display in the selected font. I’m not sure if you can index the Styles collection with string constants in C/AL. If not, you will have to iterate over all elements in the Styles collection using a numeric index and stop when the name matches. If this still doesn’t suit your needs - well, maybe you could be a bit more specific about what you consider “professional looks”. Almost everything is possible [;)]
Heinz, Your top level Excel automation variable is probably of type Application. One of the available properties at that level is Standard Font. I would do some experimenting with that property - I haven’t reset it in code before but have changed it directly in Excel via Tools => Options => Gen. Tab - Standard font. Changing it there will change the default font used by Excel in new workbooks - I would assume changing it at the App automation level would have the same effect.
Brad, you are right: there are several possible “levels” of customization in Excel. Now, when you change the standard font property in the Application object (which is the same as setting it on the Tools->Options->etc. tab), then you will change the application’s (!!!) font setting. This will be used for all new documents, as well as for all other documents where no specific font has been specified by any other means. The application setting can then be overridden by document- or cell-specific settings stored in the document. I have not tried this, but I doubt that when opening a document on another computer, the application settings in effect when the document was created will be carried over to the other computer. If my application setting is “Arial”, and yours is “Courier”, a document created by you will display in “Arial” on my computer and in “Courier” on yours.
Ok, I tried it, and I was wrong. The standard font settings are stored with the document and the document will display correctly, with the chosen font, on the other computer.
If you change the Application.StandardFont property, the users may not be very happy. Like Heinz, I also use Worksheet.Cells.Font.Name := ‘Arial’ to set all cells in a Worksheet to the same Font. It causes no problems and it isn’t unprofessional…
Cool guys, this has given us a lot to go on, Excel automation is cool, but just so badly documented it takes ages to sort stuff out. Keep the suggestions coming in 
Many thanks Meint
Ever try a creating a simple macro in excel once the data has been transfered? Sub ChangeSize() ’ Cells.Select With Selection.Font .Name = “Arial” .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub
quote:
Originally posted by meintd
Cool guys, this has given us a lot to go on, Excel automation is cool, but just so badly documented it takes ages to sort stuff out.
The best - and probably the only useful [:p] - approach is to do the stuff you want to have done manually in Excel, with macro recording switched on. Then you need to weed out all the fancy stuff Excel records together with the core functionality (like selecting a specific cell you happened to click on during recording) and translate the remaining VBA code into C/AL. The VBA documentation inside Excel is good. Not superb, but good and useful. The translation from VBA to C/AL is not only not documented at all, but in addition a heavy case of trial and error [xx(]. You will find a lot of good stuff regarding this issue in this forum, though. Several VBA features are not available in C/AL, like named parameters, and care must be taken with optional parameters. Good luck [8D]
Ok, we gave in and went the template way and it is working pretty nicely at the moment. Now we have just one issue outstanding, which is the addition of a hyperlink to a cell. Anybody able to share a line of code that shows how to add a hyperlink to an Excel cell through automation. We did the macro thing but translating that into C/AL is proving harder than expected… Thanks Meint
Something like this (tested! and it works!! [:D]) should do the trick: xlsTarget := xlsWorksheet.Cells.Item(34, 12); xlsWorksheet.Hyperlinks.Add(xlsTarget, 'http://www.microsoft.com', '', '', 'The Evil Empire'); [:p] xlsTarget is an Automation object variable of subtype Range.