Export data to excel through X++

(0) Share

Report

Posted on by Community Member

Hi All,

I am trying to export data to excel from AX through X++.

I have written a piece of code to freeze the top row of the excel while exporting using freezepanes method. Please find the code below:

xlsApplication = SysExcelApplication::construct();

com = new COM("Excel.Application");

fileName = "C:\\Test.xlsx";

xlsApplication.visible(true);

comWindow = com.activeWindow();

com.freezePanes(true);

After writing the above code,the following error popped up:

Method 'freezePanes' is not supported by the Automation interface of the COM object of class 'Excel.Application'.

I checked the following options but unfortunately unable to export data:

1) I am using excel 2013 and confirmed that it is compatible with AX2012 R2 CU7

2) I have killed all the excel sessions open and ran the code again.

3) Verified that office Ad-ins have been installed.

It will be very helpful if someone can help me on this issue.

regards,

Padmaja

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

XB 1,875 on at

Like (0)

Report

static void createExcel()
{
SysExcelCell cell;
SysExcelRange rows;
SysExcelRange row;

excel = sysExcelApplication::construct();
// excel.getSaveAsFileName("test"); //si quiremos que nos pida el nombre del archivo

books = excel.workbooks();

book = books.add();
book.saveas("C:\\Documents and Settings\\soft\\Escritorio\\EscrituraExcel.xls");

sheets = excel.worksheets();

//Crearem el worksheet
sheet = sheets.add();
sheet.name("Nombre");
columns = sheet.columns();
cells = sheet.cells();

//insertaremos las columnas
cell = cells.item(1,1); //columna y fila
cell.value("Fila 1 Columna 1");
cell = cells.item(2,4);
cell.value("Fila 2 Columna 4");

application.displayAlerts(false);
application.save();
application.quit();
}

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

Why do you believe that the Application class contains freezePanes() method? The error message says that it doesn't exist and I don't see it in documentation either.

By the way, these COM objects have nothing to do with the Excel add-in.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi All,

Thanks Martin for the inputs.

I tried to find a way on how to freeze the top row in the excel through X++ but couldn't find any solution.Is it possible in AX2012 to perform freezing of excel rows?

Please help me to resolve the issue.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Here you go, you have probably missed a couple of steps in your code:

dynamicsuser.net/.../creates-excel-file-in-x

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Vilmos,

In the link you have provided above, freezepanes method is used. But there is no such method existing in AX 2012.

The code in the link was throwing error that "Method 'freezePanes' is not supported by the Automation interface of the COM object of class 'Excel.Application'. I mentioned the same code in my question.

regards,

Padmaja

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

You're trying to call the method on the wrong object. You correctly get the active Window object (which has freezePanes property), but then you try to call the method on your com variable, which contains an Application object and freezePanes() doesn't exist there (as I pointed out before). You need comWindow.freezePanes(true).

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

The code in the link is correct, your code on the top is not.

Try instantiating the com object from you xlsApplication variable, get the comWindow from that, and after call freezepanes on comWindow as suggested above.

Was this reply helpful? Yes No
Husna Yusuf 1,094 on at

Like (0)

Report

Hi Javier,

Would you explaining more clear about your codes?

What data type/Class you used for excel and books variable?

Thank you.

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

Please note that this thread is four years old.

The type of excel variable is SysExcelApplication (as you can see from excel = sysExcelApplication::construct()).

The type of books is SysExcelWorkBooks (you can simply look at what type is returned from SysExcelApplication.workbooks().

You can also find more complete examples on the internet.

Was this reply helpful? Yes No