Skip to main content

NAV 2013 & R2 - Export to Excel Report

Hi all,

Let's see the Changes in Excel Buffer Table in NAV 2013 as compared to NAV 2009.

1. As automation are discountinued in NAV 2013, Microsoft have created a DLL for Excel Operations.

2. A new field added in Table "Cell Type", used to specifying type of field.




3. Based on the option selected the exported sheet will have the formatting.

4. The Functions have been Changed and also some functions have changed parameter.

5. A New Function which will be used in every report that is exported to Excel is - "CreateBookAndOpenExcel".

6. The Function will itself create Excel File on Service Tier, Download the File to Temp Path on Client and Open the File For User.

Let's see what changes we need to do in a export to excel report created in NAV 2009 or Previous Version to use in NAV 2013.

we have a sample report in NAV 2009 Which we used for Demo about how to export in Excel. Below is the code written on Report.

OnReportTriggers -


OnDataItem -



Now i will try to import the report in NAV 2013 and try to upgrade or compile report.

When you try to upgrade report, the error will come that you need to compile Report, But when you try to compile Report we get below error message.


The error is due to Code written to Export to Excel.

Resolution -

1. IN NAV 2009 just comment all the code Related to Export to Excel.





2. Export objects from 2009 and import it in NAV 2013.

3. Compile the Report and upgrade it (From Tools Menu - Upgrade Report).

4. Once Upgraded Change the Functions as shown below -

Specify the new field which adding column, i have also added some new fields just to show you.



Comment all code in OnPostReport and replace with single line.

ExcelBuffer.CreateBookAndOpenExcel(SheetName,ReportHeader,CompanyName,UserID2)



Save the Report and Run it.

Conclusion -
Two Major Changes in Excel Buffer.
a) One more paramerter added on AddColumn Function i.e Cell Type.
b) Only Function need to call for creating sheet and opening it is created.

There are other changes also done which will be based on requirment.

In Next post we will see how to auto save a Export to Excel Report in a specified path in the setup.

Regards,
Saurav Dhyani
www.sauravdhyani.com

Comments

  1. Hi I am using
    ExcelBuf.AddColumn("Value Entry"."Sales Amount (Actual)",FALSE,'',FALSE,FALSE,False,'',ExcelBuf."Cell Type"::Number);
    in Nav2018. Everything works well except I don't get any 1000 separator in my numbers. Is there any way to format the cell type?
    Thanks

    ReplyDelete
    Replies
    1. Just use - ExcelBuf.AddColumn("Value Entry"."Sales Amount (Actual)",FALSE,'',FALSE,FALSE,False,'#,##0.00',ExcelBuf."Cell Type"::Number);

      Delete

Post a Comment

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.