Sign in to follow this  
Followers 0
dm83737

Excel date issue (Year first no matter what)

5 posts in this topic

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

post-45921-1237306514_thumb.png

Share this post


Link to post
Share on other sites



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??

Share this post


Link to post
Share on other sites

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

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0