Excel Buffer error writing with numberformat

davmac1

I have a problem writing a moderate size Excel spreadsheet if I use numbers instead of text for the number fields.
if I write all fields as text, it creates the whole spreadsheet.
If I create the cells that only have numbers using the numberformat, it gets to row 743, column P, and fails.
It is a valid number, so that is not the problem. And it happens at the sameplace regardless of the actual data.

The exact message is:
The call to member NumberFormat failed. Microsoft Excel returned the following message:
Unable to set the NumberFormat property of the Range class.

Contents of NumberFormat: 1,175
The C/AL statement I am using:
EnterCell(RowNo,16,'',FALSE,TRUE,FORMAT(AmountDueToPrint));

I have searched Mibuso and not found this specific problem.

DenSter

Have you tried removing the comma from the number string?

davmac1

No - I can try that, but at this point it has written many numbers with commas - do you think that would make a difference?

DenSter

When you program Excel directly, you pass a decimal into the cell value and then you apply the number format. If you take 1175, and you want to format it as 1,175.00, you set the cell value to 1175, and the number format to '#,##0.00'. It's been a while since I've used the Excel buffer table, so I don't know if it'll make a difference. It might not make any difference, but it's where I would start if I were to have to troubleshoot this.

Savatage

That's how we do it.
//Convert to text using format
OnAfterGetRecord()MyPrice := FORMAT("Purchase Line"."Unit Cost ($)");
//Assign it's column & row
EnterCell(Row, 4, MyPrice, FALSE, FALSE, FALSE);
//set the cell format
EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean) : Text[250]
IF ColumnNo = 4 THEN
TempExcelBuffer.NumberFormat := '#,##0.00'
ELSE TempExcelBuffer.NumberFormat := '0';

davmac1

Thanks - I am running it that way right now. When I looked at the code, I thought I needed to put the cell contents in either the cellvalueastext or the numberformat. I am doing it the way Daniel suggested.
One question: should the format be #,###.00 even for large numbers or should it be #,###,###.00 if the number could be over a million?

davmac1

Thanks - that was the problem.