Jump to content

[SOLVED] _Excel_RangeRead & Excel Formats Question


Recommended Posts

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 by SkysLastChance

You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott

Link to comment
Share on other sites

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. 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott

Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

#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

 

You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

_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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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?

You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott

Link to comment
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
 Share

×
×
  • Create New...