Updating existing Excel Sheet

(0) Share

Report

Posted on by Ebitas

Using NAV 2016 . I have created a processing report that pulls and calculate data based on Date filter from multiple tables and companies in NAV. The report sends the results to Excel using the Excel Buffer table. The user will build charts in the Worksheet created then he or she will periodically run the processing report to update the existing data in that Worksheet without having to recreate the charts every time. I need a way to Clear the data in the worksheet before sending the updating data. I see a code in the Excel Buffer table:

XlWrkSht.Range('A3:P40').ClearContents;

I'm not sure how to use the DotNet variables like XlWrkBk and XlWrkSht inorder to use the above line of code .

Can you please help?

*This post is locked for comments

All responses (2)

Answers (1)

Suggested answer

Ebitas 70 on at

Like (0)

Report

RE: Updating existing Excel Sheet

Thank yo for your help. The link you suggested helped me a lot. I used the following code and it worked I had to add the CLEARALL at the end

XlApp := XlApp.ApplicationClass;

XlWrkBk := XlHelper.CallOpen(XlApp,FileName);

XlWrkSht := XlWrkBk.Worksheets.Item('Tech');

xlRange := XlWrkSht.Range('A3','P40');

xlRange.Activate;

xlRange.Select;

xlRange.ClearContents;

XlWrkBk.Save;

XlWrkBk.Close(TRUE,FileName,TRUE);

XlHelper.CallQuit(XlApp);

XlApp.Quit;

CLEARALL;

CLEAR(XlHelper);

CLEAR(XlApp);

CLEAR(XlWrkBk);

CLEAR(XlWrkSht);

CLEAR(xlRange);
Verified answer

I Gusti Made Ari 3,594 on at

Like (1)

Report

RE: Updating existing Excel Sheet

Please check below link for solution with same case :

community.dynamics.com/.../153134