[SOLVED] _Excel_RangeRead & Excel Formats Question

[SkysLastChance]

I am not sure what is happing at all, unfortunatlly there is no way I can put a full running code. When I enter the first and last name it works fine, However when I get to the date of birth it puts in '19760703000000' 

 

I can't figure out why "7/3/1976" is the value before the formant and "07031976" is after the format. 

 

I want it to pull the value after the format. "07031976"

 

 

$r = 1

Local $aArray = _Excel_RangeRead($oExcel, Default, Default,Default,False)

For $i = 1 To UBound($aArray) - 1   ;$i =0 Start from row A
      $sR1 = $aArray[$i][0]     ;status
      $sR2 = $aArray[$i][1]     ;first name
      $sR3 = $aArray[$i][2]     ;Last name
      $sR4 = $aArray[$i][4]     ;DOB
      $sR5 = $aArray[$i][5]     ;Email Address




WinWaitActive ("[CLASS:Notepad]")
ControlSend("[CLASS:Notepad]", "", "Edit1", $sR3 & ',' & $sR2 & @CR)
Sleep (2000)
ControlSend("[CLASS:Notepad]", "", "Edit1",("{TAB}"))
Sleep (3000)
ControlSend("[CLASS:Notepad]", "", "Edit1", $sR4 & @CR)
ControlSend("[CLASS:Notepad]", "", "Edit1",("{ENTER}"))
ControlSend("[CLASS:Notepad]", "", "Edit1",("{F12}")




$r = $r + 1
If $r > $sBox Then
   Exit
   Endif

Next

auto it demo.xlsx - excel that I am using.

Edit: I also want to mention I have tried

Local $aArray = _Excel_RangeRead($oExcel, Default, Default,3)

When I do this not even the name first and last name will write.

Edited January 30, 2017 by SkysLastChance

[water]

The value 3 reads the displayed/formatted value. Unfortunately MS only supports to read a single cell with this parameter. 

All other values return the internal representation of the date. 

Please check the wiki for a detailed description of how Excel stores dates. 

[SkysLastChance]

Hmmm okay. Is this really my best option?

 

1. Copy the original Data
2. Paste to Notepad (Prefer Notepad++)
3. Change the Cell Properties to TEXT
4. Copy All from Notepad
5. Paste back to Excel.

[water]

No. If you check the wiki you will find a script that translates the internal dates you get from _Excel_RangeRead into the date format you require.

Edited January 27, 2017 by water

[SkysLastChance]

You have did it once again. HAHA I cant figure ot how to do MM/DD/YYYY though. 

expandcollapse popup

#include <Excel.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <Date.au3>

Func _ConvertSerialDateTime($nDT) ; this function is here to convert excel date valuse to numbers
    Local Const $dtExcel = '1899/12/31'
    Local $iDate = Int($nDT) - 1 ; Adjusted after reading wiki and why result is 1 day to much: http://www.cpearson.com/excel/datetime.htm
    Local $iTime = Mod($nDT, 1)
    $iTime = Int(24 * 3600 * $iTime)
    $dtRes = _DateAdd('D', $iDate, $dtEXcel)
    $dtRes = _DateAdd('s', $iTime, $dtRes)
    Return $dtRes
EndFunc



$r = 1

Local $aArray = _Excel_RangeRead($oExcel, Default, Default,4)

For $i = 1 To UBound($aArray) - 1   ;$i =0 Start from row A
      $sR1 = $aArray[$i][0]     ;status
      $sR2 = $aArray[$i][1]     ;first name
      $sR3 = $aArray[$i][2]     ;Last name
      $sR4 = $aArray[$i][4]     ;DOB
      $sR5 = $aArray[$i][5]     ;Email Address




WinWaitActive ("[CLASS:MGUIWin]")
ControlSend("[CLASS:MGUIWin]", "", "Edit2", $sR3 & ',' & $sR2 & @CR)
Sleep (2000)
ControlSend("[CLASS:MGUIWin]", "", "",("{TAB}"))
Sleep (3000)
ControlSend("[CLASS:MGUIWin]", "", "", (_ConvertSerialDateTime($sR4) & @CR))





$r = $r + 1
If $r > $sBox Then
   Exit
   Endif

Next

 

[water]

Your case is even simpler. When you use Parameter = 1 then you get "19760703000000". This is YYYYMMDDHHMMSS.
So either use Stringformat, String* functions or a RegExp to format the string to your liking.

[SkysLastChance]

 

ControlSend("[CLASS:MGUIWin]", "", "", (_ConvertSerialDateTime & StringFormat("%02i\%02i\%04i",($sR4)) & @CR))

how far off am I here.

[water]

_ConvertSerialDateTime is not needed with the format you get (I mentioned the format in post #6).

I can't test at the moment but you need to grab the left 4 characters for year, then 2 for month and again 2 for day and then re-arrange this 3 fields in the sequence you need.

Edited January 27, 2017 by water

[SkysLastChance]

Func FormatDate($DATE)
    $SPLIT = StringSplit($DATE," ")
    $MM = StringMid($SPLIT[1],5,2)
    $YYYY = StringLeft($SPLIT[1],4)
    $DD = StringMid($SPLIT[1],7,2)
    Return $MM & "/" & $DD & "/" & $YYYY
EndFunc


ControlSend("[CLASS:MGUIWin]", "", "", (FormatDate($sR4) & @CR))

How does that grab you?

[Subz]

Or

StringFormat("%02i\%02i\%04i" ,StringLeft($sR4, 2), StringMid($sR4, 3, 2), StringRight($sR4, 4))