Hi everybody. Firstly, want to apologize if this has been posted before but I’ve search through the forum and I couldn’t find something matching exactly my problem. My customer demands to export any report from navision to excel file. I know that I can save any report to html file and then into excel file (the layout is not convenient though). I also know that I can use Automation to create excel file and transfer the data from a specific report. In my case, the requirement is to be able to convert ANY report to excel file (for example to have a single function to export reports to excel). What I need is something like the “Save as HTML…” function provided by standard navision in the file menu. The reason my customer wants this feature is because he is using an external reporting tool that takes several reports and produces various statistics. Is this possible to implement at all and if yes how can be done? Is there a fob file with similar functionality? Thanks in advance.
I’m afraid that unless someone comes up with some truly magnificent third-party solution (Crystal reports???), a simple “install and click” solution is not possible. You will have to put code into every report you want to export. For most reports, this will be simple and straightforward, but you have to do it…
Just an idea (don’t know if it works): print report as pdf-file then convert it with OCR to a Excel sheet. bye André
Hi, I just had the same problem for client. Then i found that when u use “Save as HTML” option on the Preview, u can actually give an extension as “.xls” and u have ur preview saved as excel. The format looks good for most of the list forms reports … exceptions buddy the header and footers really look bizard[:D] Hope this helps u up… cheers, JHC
Hi, Good requirment. I think we can send a request to Denmark to include this functionality in Version 4. But i came to know that this functionality will be added in Version 5 where you can save the report in text format, excel format and also may be pdf format.
Hi, The topic looks Intereseting but I had put code in as many as 15 reports before to export to Excel. excel integration is much needed and it takes much time to produce a excel integration. if Anybody has an easy approach it would be helpful if they share in the forum which benifit most of the programmers around navision world.
quote:
Originally posted by JHC
Then i found that when u use “Save as HTML” option on the Preview, u can actually give an extension as “.xls” and u have ur preview saved as excel.
A word of caution: By doing so, you will still create an HTML file which just happens to have an extension of “.xls”. The file will contain HTML code, it is definitely NOT in Excel format! Of course, you can then double-click it in explorer and it will open in Excel - but only because a) it has the “.xls” extension and b) Excel is capable of opening HTML files and display the HTML tables as an Excel table. The resulting layout will continue to range from “acceptable” to “urgh” [:D]
quote:
Originally posted by xorph Of course, you can then double-click it in explorer and it will open in Excel - but only because a) it has the “.xls” extension and b) Excel is capable of opening HTML files and display the HTML tables as an Excel table.
And then you can save it with Excel in true excel format. The result might be “urgh” sometimes, but it should keep users occupied while you work on more serious issues. [:D] Anna
I agree with Anna. Most of the time, the users will want the result in Excel so they are able to perform additional calculations. For this purpose, “urgh” should still be acceptable. [:)] If there is the requirement of having Excel reports that are “good looking”, then we have to brush up the Automation skills… [:D] Heinz is also correct. In Microsoft’s own words, Office 2000 introduced HTML as a native file format, meaning Office applications can read and write HTML as easily and precisely as their own DOC/XLS/etc. formats. The file saved from Navision as HTML with a XLS extension will nonetheless be a HTML file. … but I still wish I had the option of choosing from a few export formats (DOC,XLS,TXT and PDF come to mind…) oh well… [8)]
quote:
Originally posted by nelson
I agree with Anna. Most of the time, the users will want the result in Excel so they are able to perform additional calculations. For this purpose, “urgh” should still be acceptable. [:)]
Hm, depends on the degree of “urgh” [8D] Sometimes, there are lots of extra, empty columns. Joined cells. Headers/footers all over the place. Text in columns containing numbers. Etc. etc. [xx(] You are right, if the user wants to do some calculations on the report data, then layout is not of primary importance. But more often than not did we have to throw out all the ugly stuff in order to be able to perform these calculations. [B)]
Heinz, are you a user or a developer? [:D][:D][:D] But it’s true, the HTML parsing could really be a LOT better…
quote:
Originally posted by nelson
Heinz, are you a user or a developer? [:D][:D][:D]
Developer - fortunately [:D]. But since I am the guy who gets tarred and feathered by our users when things do not work as expected, I want our reports to be usable, pretty, efficient, good-looking, friendly and correct [:D].
Actaully, saving the HTML file with an xls extension gives a much better result than saving it with an HTML extension, when opening it in Excel. This is valid for those reports that show up really ugly in HTML because of complicated layout. Pelle
Although these suggestions are “work arounds”, automating the process into Excel is the best way. And it’s a lot of fun! [:D] Currently we put the data into the Excel Buffer table in Navision (table 370 in GB3.60), and use a few generic functions to create the document.
Connull, would you please tell us more about your way of “automating the process into Excel”? Some examples will be very appreciated! Thanks
Denis, What you need to do is dump the data into the Excel Buffer table and then use automation to create, populate and save your Excel workbook. Here is a snippet of code. First of all creating the workbook - this function resides in the Excel Buffer table; CreateBook() IF NOT CREATE(XlApplication,TRUE) THEN ERROR(Text000); XlApplication.Visible(FALSE); XlWorkBook := XlApplication.Workbooks.Add; XlWorkSheet := XlWorkBook.Worksheets.Add; Next in your report, while looping the records you want, you need to do something like … RowID := RowID + 1; XLBuffer.INIT; XLBuffer.VALIDATE(“Row No.”,RowID); XLBuffer.VALIDATE(“Column No.”,1); XLBuffer.“Cell Value as Text” := “First Name”; XLBuffer.ReportID := ‘51094’; XLBuffer.INSERT(TRUE); XLBuffer.INIT; XLBuffer.VALIDATE(“Row No.”,RowID); XLBuffer.VALIDATE(“Column No.”,2); XLBuffer.“Cell Value as Text” := Surname; XLBuffer.ReportID := ‘51094’; XLBuffer.INSERT(TRUE); … To find out more information about the Excel DOM, visit the following site; http://msdn.microsoft.com/default.aspx and do a search for “Excel automation” and / or “Excel DOM”. [;)] Good luck! [:D]