Jump to content
SkysLastChance

[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

Life's simple. You make choices and you don't look back.

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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

 


Life's simple. You make choices and you don't look back.

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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?


Life's simple. You make choices and you don't look back.

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

  • Similar Content

    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
    • By rdaneelol
      I have a strange behavior in an Autoit program.  
      The program works perfectly in the original environment I created the program for - for Windows 7 and Office  2010.
      My workplace is migrating to Windows 10 with Office 2016.  When I run this program in that new environment,  the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running.  If you exit the program, the excel process ends at that point...
      I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me !
      While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful. 
      I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close.
      Any ideas on why an excel close would hang until program exit ?
       
      Local $oExcel = _Excel_Open(False, False, False, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true ) If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel, False, False)  Exit EndIf ;  this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right... With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)     $Match = .Find (@UserName)     If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0)    Then         MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist.         ;_Excel_BookClose($oWorkbook, False)         _Excel_Close($oExcel, false, false)         Exit     Else         Local  $cResult = $Match.Offset(0, 2).value     EndIf EndWith $oWorkbook.Saved = True _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016  
    • By LxP
      I'm trying to pass a nested array to a function, such that the function alters the inner array.
      I was surprised to find that this minimal reproducible example, despite its use of ByRef, seems to pass a copy of the inner array to the function:
      #include <Array.au3> ; a boring old array Local $aInnerArray[5] = [1, 2, 3, 4, 5] ; a one-element array containing a reference to the other array Local $aOuterArray[1] = [$aInnerArray] ; intention: take a nested array and alter its inner array ; reality: the inner array seems to be getting copied Func ChangeIt(ByRef $aOuter) Local $aInner = $aOuter[0] $aInner[2] = 0 EndFunc ; Expected: [1, 2, 3, 4, 5] ; Actual: [1, 2, 3, 4, 5] ✔ _ArrayDisplay($aInnerArray, 'Before') ; $aOuterArray passed by-ref, should receive reference to $aInnerArray ; Therefore should change $aInnerArray to [1, 2, 0, 4, 5] ChangeIt($aOuterArray) ; Expected: [1, 2, 0, 4, 5] ; Actual: [1, 2, 3, 4, 5] ✘ _ArrayDisplay($aInnerArray, 'After') I suspect that either:
      the copy is taking place in the first line of the function (I couldn't find a way to access the inner array without first assigning it to a variable though); or ByRef doesn't propagate into inner levels of the data structure being passed, which seems less likely to me. Could someone please point me in the right direction to get this working as intended?
      Update: the answer
      ; WRONG: ; a one-element array containing a reference to the other array Local $aOuterArray[1] = [$aInnerArray] The assumption I made about this code is wrong—it actually copies $aInnerArray into $aOuterArray, so there are now two unrelated $aInnerArray instances.  It is not possible to store arrays in other arrays by reference.
      If it is necessary to refer to a mutable array in multiple places, consider holding it in a global variable.  Where a collection of mutable arrays needs to be accessed in multiple places (as in my case), consider storing them in a global array and referring to each sub-array by index (also known as the Registry pattern).
    • By VinMe
      HELLO All,
      is there any function in Auto it to Remove the repeating  strings separated by ","  which is present in the array.
      ex.
      Available state
      a[0]= D97,D96,,D85,D86,D85,D86,D85,D86,D85,,D86,D85,D86,D85,D86
      a[1]=D85,D24,,,,,D85
      Required state
      a[0]= D97,D96,D85,D86,
      a[1]=D85,D24
      thank  you in advance!
      vin!
    • By VinMe
      $aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving...
      456.xlsx
×
×
  • Create New...