Updating an existing worksheet in Microsoft Dynamics NAV

In our last week’s blog we went through the process of generating a report into a new excel file. Click here to view on how to print reports in excel

The article below will show us on how to export values into an existing worksheet/workbook. This case is mostly applicable in scenarios where we don’t want to create new files every time when we generate a report.

The following scenario explains us where we already have a worksheet and we need to use the same excel workbook and use the worksheet that was previously used

A Sample image below indicates the existing excel book with existing sheet has some values:

How can we achieve this?

1.       Open the excel file by specifying the file path.

2.       Specify the excel sheet name.

3.       Clear the already existing contents.

4.       Export the values in the same worksheet

The following code drives the above steps:

//xlapp – Automation – Microsoft Excel 12.0 Object Library. Application

//xlbook – Automation – Microsoft Excel 12.0 Object Library. Workbook

//xlsheet – Automation – Microsoft Excel 12.0 Object Library. WorkSheet

//filepath – Text [1024]

 

Filepath:=’<Your Filepath>’; //Specify the location where the excel file is stored locally

IF ISCLEAR(xlapp) THEN

  CREATE(xlapp);

 

xlapp.visible(true);

 

//1. Opening the excel file

xlbook:=xlapp.Workbooks.Open(filepath);//specify your file path with the filename.xls(x)

 

xlbook:=xlapp.ActiveWorkbook;

 

//2. Specify the excel sheet name

xlsheet := xlbook.Worksheets.Item(‘<your Work Sheet Name>’);

 xlsheet.Activate();

 

//the below code will be useful when the excel sheet is already filtered based on some condition

// if it is filtered then we need to show all the data in the excel sheet.

 IF xlsheet.FilterMode THEN

    xlsheet.ShowAllData;

 

//3. Clearing the existing contents within worksheet

 xlsheet.Range(‘A1:C1048576’).ClearContents;

// Note: In this scenario we have specified C because we have only 3 columns and 1048576 denotes the max no of rows in excel 2007 and later

 

//4. Exporting the new values

xlsheet.Range(‘A1’).value:=’No.’; //1^st Column Name

xlsheet.Range(‘B1’).value:=’Customer No; //2^nd column Name

xlsheet.Range(‘C1’).value:=’Sales Person Code’; //3^rd Column Name

 

//Exporting the sales header values

reccount:=2;   //where variable reccount - Integer

salesheader.RESET;  //where variable salesheadaer - Record - Sales Header var

IF salesheader.FINDSET THEN

REPEAT

  xlsheet.Range('A'+FORMAT(reccount)).Value:=salesheader."No.";

  xlsheet.Range('B'+FORMAT(reccount)).Value:=salesheader."Sell-to Customer No.";

  xlsheet.Range('C'+FORMAT(reccount)).Value:=salesheader."Salesperson Code";

  reccount:=reccount+1;

UNTIL salesheader.NEXT=0;

 

Xlbook.save;

 

After executing the code, the existing excel file will be opened automatically so that we can view the excel sheet with new/replaced values. The below image refers the values exported from a Sales Header table.