Read Range: Not a legal OleAut date

Hi I’m getting an Error of Read Range: Not a legal OleAut date. while reading an excel file when i change format of excel to general it changes date from 1/27/2020 to 43857 any solution?

you may have a date very old like 1/1/1800…

@apurvalost - this might help: How to solve error: "Read range : Not a legal OleAut date." in excel?

@sumitd and @bcorrea thats not a solution as I can’t change excel spread sheeet not even format.

if you do have that kind of date, then you will be without luck… unless you want to read cell by cell and build your own datatable using text for those dates…

Date is not old But when i change format from date to general or something else then instead of date it becomes a decimal number , so excel spread sheet data is changed . Hope you get what i mean @bcorrea

i understand, when you select one of date cell what do you see up in the formula box?
image

@bcorrea

so it is originally a date and you want it to be general? or without changing anything you are getting that error?

Yes orginially its date if i change it to general uipath can read it but doing so it messed up my orginial excel data which is not what we want.

image @bcorrea

that is wrong to me, are you sure ALL cells are really Date? dont you have any with a text instead with like an empty space hidden, or even reading the last line blank?

two coloumn are date while other i can make it general even though they are number and text @bcorrea

i mean i am quite sure you must be missing something wrong with the data in the Date column… at least one of the rows must be wrong and you cant see it…

I think i need to change excel from date to =text(value,“mm/dd/yyyy”) but not sure it ideal

im sure if you check you excel or the range you are reading you will find where the error cell is… because it being the Date format, you should not have that error…

I suggest you using call VBNET code to read the cell or range of spreadsheet and specific the format of cell you want.

@apurvalost So you cannot read the Excel file when the Format is Date in Excel , is that right?

I have figured it out there were changes in format while making excel data sheet once corrected as per format it works thanks a lot @supermanPunch @bcorrea @sumitd

3 Likes

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