Skip to main content

NAV 2013 & NAV 2013 R2 - Save Export to Excel

Hi all,

In Last post we had seen how to use export to Excel Reports in NAV 2013.

The Next question asked to me is with older version with some code we used to able to save the Report at a specified path instead of opening it. Is it possible with NAV 2013 and later?

So here it is, we can again create some functions in excel buffer and save the excel files.

This article Applies to Microsoft Dynamics NAV 2013 & NAV 2013 R2. Attached Screens are from NAV 2013 R2.


Steps 

SETUP -

1. I created a New Field in Table 409 SMTP Mail Setup for Specifying the Path.



2. Added the Field on Page 409 SMTP Mail Setup as shown below.



Changes in Table 370 Excel Buffer -

1. Created Two Functions CreateBookAndSaveExcel & SaveExcel.

2. Function CreateBookAndSaveExcel Definition- 
 * Copy of CreateBookandOpenExcel with some code changed.


CODE -
-------------------------------------------------------------------------------------------------------------
CreateBook(SheetName);
WriteSheet(ReportHeader,CompanyName,UserID2);
CloseBook;
SaveExcel;
-------------------------------------------------------------------------------------------------------------

3. Function SaveExcel Definition -



CODE
-------------------------------------------------------------------------------------------------------------
SmtpSetup.GET;
IF OpenUsingDocumentService('') THEN
  EXIT;

IF NOT PreOpenExcel THEN
  EXIT;

FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,'Book1.xlsx',SmtpSetup."Save Excel Report");
-------------------------------------------------------------------------------------------------------------

Variables in Function Save Excel-

Name DataType         Subtype
FileNameClient Text
SmtpSetup  Record SMTP Mail Setup

Changes in Report 120 Aged Accounts Receivable -

In Function CreateExcelbook New Function call (save Instead of Open)



When i Run the Report it will get saved in D Drive (path Specified in SMTP Setup).

Changes have been done on four objects. Objects can be downloaded from -

Dynamics User Group NAV For 2013 and  Dynamics User Group NAV For 2013 R2

or my Skydrive. There are two files one for NAV 2013 and one for NAV 2013 R2.

NAV 2013 File Name  - NAV 2013_Save to Excel
NAV 2013 R2 File Name - NAV 2013 R2 _ Save to Excel

You can also make it more generic by passing File Name from the Report Itself.

Regards,
Saurav Dhyani

Comments

Popular posts from this blog

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 - 

Dynamics 365 Business Central 2021 release wave 2 (BC19) Released.

Hi Readers, As we all were expecting Microsoft have Released Business Central 2021 Release Wave 2 Today a.k.a BC19. In this article we will see what all is announced with the General Availability.  Starting Today Customers & Partners can start using Business Central 19. #msdyn365bc #bc19

Navision Cannot generate SSPI context State ID HY000

Hi all, Sometime while trying to connect database in classic client with windows authentication or connect to Role Tailored Client we face an error message. Error - The following ODBC error occurred: Error: [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context  State ID: HY000