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:
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:
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…