Excel date issue (Year first no matter what)

[dm83737]

I am reading a cell from Excel for the date of birth for clients that gets entered into another application; this date reads as 07041959 within Excel (formatted as this custom setting for the cells = mmddyyyy). When AutoIT gets to the point of entering that cell's contents to the other application, it inputs 19590704 (yyyymmdd). I have tried changing the custom cell setup to yyyymmdd to see if maybe it would read it backwards and therefore correct itself, but it once again entered 19590704. So now I am thinking it has something to do with the fact that the cell is of the Date type and that somehow makes AutoIT behave a certain way. So I am hoping there is a way to override this and get it to enter the date as 07041959. My last option would be to have those cells be formatted as text, but that forfeits other functionality I have on this spreadsheet that depends on those dates.

I am pretty sure this is another easy one that I just cant seem to put my finger on, so I know there will be a quick resolution from one of the wise ones around here. I have attached a picture to illustrate what I am talking about.

This is the pieces of code I am using. As you can see, there is nothing special about them.

For $iRowRange = 2 To 999
$iDOB = _ExcelReadCell($oExcel, $iRowRange, 4)
...
Send($iDOB)

* Obviously much more code within the script that is not relevant here

[erik7426]

I am reading a cell from Excel for the date of birth for clients that gets entered into another application; this date reads as 07041959 within Excel (formatted as this custom setting for the cells = mmddyyyy). When AutoIT gets to the point of entering that cell's contents to the other application, it inputs 19590704 (yyyymmdd). I have tried changing the custom cell setup to yyyymmdd to see if maybe it would read it backwards and therefore correct itself, but it once again entered 19590704. So now I am thinking it has something to do with the fact that the cell is of the Date type and that somehow makes AutoIT behave a certain way. So I am hoping there is a way to override this and get it to enter the date as 07041959. My last option would be to have those cells be formatted as text, but that forfeits other functionality I have on this spreadsheet that depends on those dates.

I am pretty sure this is another easy one that I just cant seem to put my finger on, so I know there will be a quick resolution from one of the wise ones around here. I have attached a picture to illustrate what I am talking about.

This is the pieces of code I am using. As you can see, there is nothing special about them.

For $iRowRange = 2 To 999
$iDOB = _ExcelReadCell($oExcel, $iRowRange, 4)
...
Send($iDOB)

* Obviously much more code within the script that is not relevant here

I can't recreate your problem. In fact, I am having trouble formatting an excel field as you have. I did create an excel file and put dates in column D such as 07041959 and 05111975 and when using this code:

#include <Excel.au3>

$sFilePath1 = @ScriptDir & "\Test1.xls";This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)

For $iRowRange = 2 To 999
    $iDOB = _ExcelReadCell($oExcel, $iRowRange, 4)
    If $iDOB <> "" Then
        MsgBox(0, "", $iDOB)
    EndIf
Next
_ExcelBookClose($oExcel, 1, 0)

I get the date returned in the message box exactly as it has been put in Excel. Perhaps if you could post more code or perhaps a sample of part of your Excel file??

[Bowmore]

Try this slightly modified version of _ExcelReadCell().

It should return the contents of the cell as it appears in Excel rather than the underlying value

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Text
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Text
    EndIf
EndFunc   ;==>_ExcelReadCell

[TimCunningham]

This fixed my issue that I was having. Thanks for the code.

Try this slightly modified version of _ExcelReadCell().

It should return the contents of the cell as it appears in Excel rather than the underlying value

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Text
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Text
    EndIf
EndFunc ;==>_ExcelReadCell

[water]

This thread is 4 years old. Isn't it a bit late?