What code is to be used to automatically save to excel, rather than printing to excel when running report. (In Windows Dynamics NAV Classic 2009)

Can Anyone sent me the syntax and the exact code to be used in the report C/AL Code, inorder to automatically save the files to excel format when the report is being run.

(Microsoft Dynamics NAV Classic 2009)

<tags removed, and text reset to regular font size by moderator>

Renjit, you need to stop with the irrelevant tags, and posting in large bold fonts. Everyone in here can perfectly read your question in regular size.

Hi Renjith,

Follow this link from where you can download a form object to save the file in excel format. I think this wold help you. Ask me for further clarification.

http://cid-1c1c99d247edd270.office.live.com/self.aspx/Navision%20Objects/Excel%20Integration.fob

Take a look at this Aged Accounts Receivable report. It has everything you are looking for.

Hi matt,

i Checked the aged account Receivable, but when i run the report, i can only print to excel. I cant save it automatically.

Actually iam looking for this because, i want to automate the entire report generation and save to excel, by using job scheduler in microsoft dynamics nav classic 2009. Actually iam confused with the code that i have to write inside the C/AL coding. Hope u understand what iam looking for.

I tried with the help of automation variables xlApp and xlBook, But when i run the report, a blank excel file get saved to the location, and then after that the report in excel format will be opened, Iam not able to save the orginal one having the data in it. Can you suggest a solution for this?

Cheers!!!

Hi Karthik,

I cannot install the FOB file that you have sent to me. Can u explain me how to use that FOB file because, i have no idea of that one.

With Regards

Renjith

H Renjith,

You can export the data to excel. What you need is save that excel file automatically right?

Then use this code.

xlBook.SaveAs(‘c:\karthik.xls’);

where xlBook is an automation server of subtype ‘Microsoft Excel 11.0 Object Library’.Workbook

I think this would help you.

Hi Karthik,

I wrote this code inside the Aged account Receivable (120).

CreateExcelbook()

ExcelBuf.CreateBook;

ExcelBuf.CreateSheet(Text011,Text012,COMPANYNAME,USERID);

ExcelBuf.GiveUserControl;

xlBook.SaveAs(‘H:\karthik.xls’);

ERROR(’’);

But it is saving a blank excel workbook with the name karthik. What can i do? Can u copy and paste the code plz.

With Regards

Renjith

Hi Karthik,

And i have only MIcrosoft Excel 5.0 Object library and Microsoft Excel 12.0 Object library. I dont have Microsoft Excel 11 Object library. What should i do:?

Cheers!!!

Renjith

Hi Karthik,

When i put that x1.SaveAs(‘C:\ Aged’); , It is showing an error message:

The Message is for C/AL Programmers

(This Automation Variable has not been instantiated. You can instantiate it by either creating or assigning it)

Cheers!!!

Hi Renjith,

Let me tell you sample code to export to excel and to save it.

CREATE(xlApp);
xlBook := xlApp.Workbooks.Add(-4167);
xlSheet:= xlApp.ActiveSheet;
xlSheet.Name := ‘Personnel Expenses’; // To print the sheet name

cust.RESET;
cust.SETFILTER(cust.“No.”,‘10000’);
IF cust.FIND(’-’) THEN
cust.CALCFIELDS(cust.“Sales (LCY)”);

xlSheet.Range(‘A2’).Value := ‘10000’; // To export the value from navision to excel
xlSheet.Range(‘A3’).Value := cust.“Sales (LCY)”; // To export the value from navision to excel

xlApp.Visible := TRUE;
xlBook.SaveAs(‘c:\karthik.xls’); // To save the exported data in excel file

Hi Karthik,

Thankz for that…iam making new code, your above code is very helpful for me.!!thank u

Hi Karthik,

Thank you very much for the solution. It works perfectly. I was looking for this for sometime and finally got it from this post.

Thank you very much,

Cheers!

Create a function “EnterCell” with the variables as follows:

Var Name DataType Subtype Length

RowNo Integer

ColumnNo Integer

CellValue Text 100

Bold Boolean

Underline Boolean

NumberFormat Text 30

Italic Boolean

and in the body of the function write this code:

ExcelBuf.INIT;

ExcelBuf.VALIDATE(“Row No.”,RowNo);

ExcelBuf.VALIDATE(“Column No.”,ColumnNo);

ExcelBuf.“Cell Value as Text” := CellValue;

ExcelBuf.Bold := Bold;

ExcelBuf.Underline:=Underline;

ExcelBuf.Italic:=Italic;

ExcelBuf.NumberFormat := NumberFormat;

ExcelBuf.INSERT;

NOW put this code on these following trigeers:

Report - OnInitReport()

PrintToExcel:=FALSE;

RowID:=3;

Report - OnPreReport()

IF PrintToExcel THEN

ExcelBuf.DELETEALL(TRUE);

GLFilter := “G/L Account”.GETFILTERS;

GLDateFilter := “G/L Account”.GETFILTER(“Date Filter”);

Report - OnPostReport()

IF PrintToExcel THEN BEGIN

ExcelBuf.CreateBook;

ExcelBuf.CreateSheet(‘f form detail’,‘f form detail’,COMPANYNAME,USERID);

ExcelBuf.GiveUserControl;

END;

Now put this code on the OnPresection of every part, I mean Header(1),Header(2), Body,etc. as per the content in those.

In my case, I am putting you the code for header…You have to follow accordingly to manage your rows.

IF

PrintToExcel THEN BEGIN

RowID+=1;

EnterCell(RowID,1,‘Detail Trial Balance’,TRUE,TRUE,’@’,TRUE);

EnterCell(RowID,8,FORMAT(TODAY,0,4),TRUE,TRUE,’’,TRUE);

RowID+=1;

EnterCell(RowID,1,FORMAT(STRSUBSTNO(Text000,GLDateFilter)),TRUE,TRUE,’’,TRUE);

EnterCell(RowID,8,‘Page’,TRUE,TRUE,’@’,TRUE);

EnterCell(RowID,9,FORMAT(CurrReport.PAGENO),TRUE,TRUE,’’,TRUE);

RowID+=1;

EnterCell(RowID,8,FORMAT(COMPANYNAME),TRUE,TRUE,’’,TRUE);

EnterCell(RowID,9,FORMAT(USERID),TRUE,TRUE,’’,TRUE);

RowID+=2;

END;

I hope u will easily be able to do it…All the best !