How to change the version of Excel using X++ code

Hi,

How to change the version of the excel using X++ code.

Ex: Now I have a version 2013 and i want to change the excel version to 2009 then how to change the version of Excel using code.

From xlsx to xls?
If so try using\Classes\SysExcelWorkbook\saveAs

Hi,
I will try.

Hi,

I am getting below Error “Method ‘value’ in COM object of class ‘Range’ returned error code 0x800A03EC () which means: .”.

This is the sample code but while ruining above error occurs.

FileName fileName = @‘C:\Users\Desktop\Sample.xls’;

// Variabler til Excel
SysExcelApplication sysExcelApplication;
SysExcelWorksheet sysExcelWorksheet;
SysExcelWorksheets sysExcelWorksheets;
SysExcelWorkbooks sysExcelWorkbooks;
SysExcelWorkbook sysExcelWorkbook;
SysExcelCell sysExcelCell;
counter excelRow;

// Open the Excel document
sysExcelApplication = SysExcelApplication::construct();
sysExcelWorkbooks = sysExcelApplication.workbooks();

//specify the file path that you want to read
try
{
sysExcelWorkbooks.open(filename,0,false);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

sysExcelWorkbook = sysExcelWorkbooks.item(1);
sysExcelWorksheets = sysExcelWorkbook.worksheets();
sysExcelWorksheet = sysExcelWorksheets.itemFromNum(1);

excelRow = 2;

SysExcelCell = SysExcelWorksheet.cells().item(excelRow,1);
SysExcelCell.value(“Col1”);

SysExcelCell = SysExcelWorksheet.cells().item(excelRow,2);
SysExcelCell.value(“Col2”);

SysExcelCell = SysExcelWorksheet.cells().item(excelRow,3);
SysExcelCell.value(“Col3”);

sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.saved(true);
sysExcelApplication.visible();

Please suggest me to resolve the error.

If you want to change from xlsx to xls:

Your code should be:

FileName fileName = @'C:\Users\Desktop*Sample.xlsx’;*

sysExcelWorkbook.saveAs(@‘C:\Users\Desktop**Sample.xls’**);
sysExcelWorkBook.saved(true);
sysExcelApplication.visible();

Hi,
I tried but same error “Method ‘value’ in COM object of class ‘Range’ returned error code 0x800A03EC () which means: .” is displayed.Can you suggest me to resolve the error.

Were you able to identify Which line of code is causing that error?

Hi,

Getting error in value method of “SysExcelCell.value(“Col1”)” this line while debugging.

class—>SysExcelCell_XP —>value()

public COMVariant value(anytype _value = COMArgument::NoValue)

{

COMVariant ret;

;

if (!prmisdefault(_value))

{

ret = cell.value(#xlRangeValueDefault,_value); // Here I am getting Error

}

else

{

ret = cell.value(#xlRangeValueDefault);

}

return ret;

}

I tried your code and i don’t see any such error.

Hi,
May i know which version your using for Excel.

Are you trying to replace existing value in 2nd row (col1, col2, col3) ?

Hi,
No…Above is sample code,I trying to Export to excel but excel is not opening, above error is displayed.
Actual Code I am using for Export to Excel is see the code below.

SysExcelApplication application;
SysExcelWorkBooks workbooks;
SysExcelWorkBook workbook;
SysExcelWorksheets worksheets;
sysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
SysExcelCell cell1;
str fileName;
int row=2,rowcal= 2;
int i,j;
COM comObj, intObj;
SecurityTask SecurityTask,SecurityTask1,securityTaskRecIdRow,securityTaskRecIdCol;
SecuritySegregationOfDutiesRule SecuritySegregationOfDutiesRule;

int col = 2,colinc =2;
str 50 strcell1,strcell2,strcell3,strcell4;
int countfield;
int iloop,jloop;
;
select count(RecId) from SecurityTask1 where SecurityTask1.Type == SecurityTaskType::Duty;

application = SysExcelApplication::construct();
workbooks = application.workbooks(); //gets the workbook object
workbook = workbooks.add(); // creates a new workbook
worksheets = workbook.worksheets(); //gets the worksheets object

worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();

while select SecurityTask
where SecurityTask.Type == SecurityTaskType::Duty
{
cells.item(row,1).value(SecurityTask.Name);
cells.item(1,col).value(SecurityTask.Name);

strcell1 = cells.item(row,1).value().bStr();
strcell2 = cells.item(1,col).value().bStr();
if(strcell1 == SecurityTask.Name && strcell2 == SecurityTask.Name)
{
comObj = cells.item(row,col).comObject();
intObj = comObj.Interior();
intObj.color(000000);//#D3D3D3
}
col++;
row++;
}
for(iloop =1;iloop<=SecurityTask1.RecId;iloop++)
{
for(jloop = 1 ;jloop <=SecurityTask1.RecId ;jloop++)
{

strcell3 = cells.item(rowcal,1).value().bStr();
strcell4 = cells.item(1,colinc).value().bStr();
if(strcell3 != strcell4)
{

select securityTaskRecIdRow where securityTaskRecIdRow.Name == strcell3;
select securityTaskRecIdCol where securityTaskRecIdCol.Name == strcell4;
select SecuritySegregationOfDutiesRule where SecuritySegregationOfDutiesRule.FirstSecurityTask == securityTaskRecIdRow.RecId &&
SecuritySegregationOfDutiesRule.SecondSecurityTask == securityTaskRecIdCol.RecId;
if(SecuritySegregationOfDutiesRule.Severity == SegregationOfDutiesSeverity::High)
{

comObj = cells.item(rowcal,colinc).comObject();
intObj = comObj.Interior();
intObj.color(255);
}
else if (SecuritySegregationOfDutiesRule.Severity == SegregationOfDutiesSeverity::Medium)
{

comObj = cells.item(rowcal,colinc).comObject();
intObj = comObj.Interior();
intObj.color(16711680);//#D3D3D3
}
else if(SecuritySegregationOfDutiesRule.Severity == SegregationOfDutiesSeverity::Low)
{

comObj = cells.item(rowcal,colinc).comObject();
intObj = comObj.Interior();
intObj.color(2551650);//#D3D3D3
}

}
colinc++;
}
rowcal++;
}
application.visible(true);

Hi,
Please someone Suggest me to resolve the error.

It seems you are trying to get security related information,
Can you try in alternate way:
Create one query with your tables and add your conditions as ranges, construct simple form using that query and add command button and choose its command property as (Export to Microsoft Excel: 4436).

Hi,
Yes ur correct but i want to export the security related information directly to Excel. can you suggest me in direct way and how to resolve that error.

Hi,
Please some one suggest me to resolve above Error.