Excel Application Scope change values which are not in the same scale

Help Activities
,
1

Dear friends,

I am in a dire need to find a solution of the following problem:
I have an .xls excel file which is downloade from SAP. Sometimes the value in each row are not in the same scale. What I meant with that, is that usually we have values in thousands, but there is also a situation where we got one or two rows with value less than 1000.
The example is best described in picture 1:
image
In this case we got one value of 948 which is of type “General”.
Now, when I open this file manually, I can easily change this and make sure that the format becomes correct again.
Problem is, when I open this with Excel application Scope, it assumes that this value is also in thousand, and thus this is what I got:
image
It’s even doing wrong for other columns in the same row, but I only care that particular column as that is the one that I need to work on.
My temporal solution is to loop for each row and look if there is any row in that column which ends with “.000” and if yes, change it to “,000”.
However, I dont think this solution hold proof as i am afraid that it will make mistakes for other cases.
Using VBA to manipulate the datatable wont help, as the file is already read wrongly since ExcelApplicationScope opens it.
If someone can help me, I would be very grateful…for the sake of automation, please help me…

@ppr, @GBK : I always follow your answer and use your method of working. I hope you have time to have a look into this matter?

2

Hi @NazAutomate !
I don’t know if it may help, but I found this option, maybe you should give it a try:
image

3

Hi @NazAutomate ,

Could you try with the below Blareva excel activites. thanks.

BalaReva.EasyExcel.Activities

Copy Cells Format

Source-> your column cell reference eg: E2

Destination → you could use entire range of the column from second row to last
eg: E3:E+rowcount

4

Thank you for the suggestion, everyone.
As I already told before, the Excel is already read incorrectly when Excel Application Scope read it.
I wll stick to my solution at the moment.

Happy holidays.

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.