Friday, May 10, 2013

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
//1. Opening the excel file
xlbook:=xlapp.Workbooks.Open(filepath);//specify your file path with the filename.xls(x)
//2. Specify the excel sheet name
xlsheet := xlbook.Worksheets.Item(‘<your Work Sheet Name>’);
//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
//3. Clearing the existing contents within worksheet
// 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.’; //1st Column Name
xlsheet.Range(‘B1’).value:=’Customer No; //2nd column Name
xlsheet.Range(‘C1’).value:=’Sales Person Code’; //3rd 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
  xlsheet.Range('B'+FORMAT(reccount)).Value:=salesheader."Sell-to Customer No.";
  xlsheet.Range('C'+FORMAT(reccount)).Value:=salesheader."Salesperson Code";
UNTIL salesheader.NEXT=0;;
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.

No comments:

Post a Comment