Masum

ExcelRangeRead intermittent error

3 posts in this topic

Hi all,

I'm facing a random error, whereby the ExcelRangeRead function is storing the first letter of a string as a lowercase rather than the uppercase it is stored in Excel. This happens about 10% of the times. And only with one of the ExcelRangeRead functions below, Global $RTERuleName = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'I' & $Row)

Func ExtractDCWData()
    Global $PoolName = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'G' & $Row)
    Global $PoolDescription = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'H' & $Row)
    Global $RTERuleName = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'I' & $Row)
    Global $FirstName = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'F' & $Row)
    Global $LastName = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'E' & $Row)
    Global $Notes = _Excel_RangeRead($oWorkbook, 'CHFT Pools & RTE Rules', 'L' & $Row)
    Sleep(200)
EndFunc   ;==>ExtractDCWData

The excel column looks like this.

Capture.PNG

The script that writes the data out is as follows.

Func AddRTERule()
    MouseClick('Primary', 69, 657, 1) ;Click Rules...
    Sleep(200)
    MouseClick('Primary', 363, 228, 1) ;Click Type
    Sleep(200)
    MouseClick('Primary', 363, 266, 1) ;Click RTE
    Sleep(200)
    MouseClick('Primary', 363, 545, 1) ;Click New Rule
    Sleep(200)
    Send($RTERuleName) ;Enter Mnemonic
    Sleep(200)
    Send('{TAB}') ;Enter Description
    Sleep(100)
    
    ;Theres more lines of code in this function...
    
EndFunc   ;==>AddRTERule

Any ideas?

 

Share this post


Link to post
Share on other sites



What you get from _Excel_RangeRead is the RAW VALUE of the cell. What you see on the screen is the FORMATTED VALUE.
Make sure that there is no formatting rule on the cells setting the first letter to uppercase.


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

Thanks there shouldn't be, but I'll clean it all up and try again.

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

    • nikink
      By nikink
      Hi all,
      I have a bit of code that works on my old Win10 PC, that fails on my new Win10 PC, and I think the only significant difference is the version of Autoit - old PC has 3.3.12, new has 3.3.14.
      I couldn't find anything mentioned in the change logs though, so perhaps I'm wrong.
      Anyway, the code to replicate my issue is:
      Test('username', 'DOMAIN') ; THIS ERRORS: ;Test('localun', 'DOMAIN') ; THIS ERRORS: ;Test(' ', ' ') ; THIS ERRORS: ;Test('', '') ; THIS ERRORS: ;Test('localun', '') ; THIS ERRORS: ;Test('', 'DOMAIN') Func Test($un, $dom) $compName = 'PCNAME' $FullName = '.' $Description = '.' ; get the WIM object $objWMIService = ObjGet("winmgmts:\\" & $compName & "\root\cimv2") ; get default user full name and description $objAccount = $objWMIService.Get("Win32_UserAccount.Name='" & $un & "',Domain='" & $dom & "'") If IsObj($objAccount) Then $FullName = $objAccount.FullName $Description = $objAccount.Description EndIf ConsoleWrite($FullName & @CRLF) ConsoleWrite($Description & @CRLF) Return EndFunc  
      On my old PC this code will output just . and . for each of those line currently commented out. Which is fine.
      On my new PC any of those commented out lines of code cause an error, and the script won't even compile.
      $objAccount = $objWMIService.Get("Win32_UserAccount.Name='" & $un & "',Domain='" & $dom & "'") $objAccount = $objWMIService^ ERROR I'm very much a newb with the WMI stuff and objects, but it looks like the .Get property is failing when either $un or $dom aren't valid in v3.3.14, whereas in 3.3.12 the .Get would fail to return an object, which is then caught by the If statement.
      Am I on track with this? Is there some new/better way to code the example so that 3.3.14 will compile it?
    • argumentum
      By argumentum
      UDF to intercept the error window of AutoIt, showing more details about the error, including ability to save and send by email!
    • SkysLastChance
      By SkysLastChance
      I get this error, Whenever I try to find a date. Does anyone have any idea why? I saw some post from 2015, However I would imagine this is fixed by now. @water
      >"J:\Temporary Files\XXXXXXXXX\AutoIt\AutoIt\AutoIt\SciTe\..\autoit3.exe" /ErrorStdOut "C:\Users\XXXXXX\Desktop\Call Report Automation.au3"     "J:\Temporary Files\XXXXXXXX\AutoIt\AutoIt\AutoIt\Include\Excel.au3" (656) : ==> The requested action with this object has failed.: $aResult[$iIndex][1] = $oMatch.Name.Name $aResult[$iIndex][1] = $oMatch^ ERROR >Exit code: 1    Time: 5.791 #include <Excel.au3> Global $sExcelFile1 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsm)") Global $sExcelFile2 = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "Excel Sheet (*.xlsx;*.xls)|All (*.*)") If FileExists($sExcelFile2) Then Global $oExcel2 = _Excel_Open () $oExcel2 = _Excel_BookOpen($oExcel2,$sExcelFile2) EndIF If FileExists($sExcelFile1) Then Global $oExcel1 = _Excel_Open () $oExcel1 = _Excel_BookOpen($oExcel1,$sExcelFile1,Default,Default,"2007") EndIF $oRead = _Excel_RangeRead ($oExcel2,Default,"A2",3) _Excel_RangeWrite ($oExcel1,"Calls Handled",$oRead,"BY7") Sleep (1000) _Excel_RangeFind ($oExcel1,$oRead,"E4:FD92") MsgBox (0,"Test",$oRead) UPDATE:
      If I take out this line it works. By works I mean I don't get the error. :/  However, I need it. I am just confused.
      _Excel_RangeWrite ($oExcel1,"Calls Handled",$oRead,"BY7")  
      UPDATE 2: I also get this error when trying to use the helpfile examples. I have version 3.3.14.2
      \AutoIt\AutoIt\AutoIt\Include\Excel.au3" (670) : ==> The requested action with this object has failed.: $oSheet = $oWorkbook.Sheets($iIndexSheets) $oSheet = $oWorkbook^ ERROR >Exit code: 1 Time: 0.8931  
    • SkysLastChance
      By SkysLastChance
      How would I go about writing a IF Then statement if the conrol send fails?
      Run ("Notepad.exe") WinWaitActive ("Untitled - Notepad") ControlSend("Untitled - Notepad", "", "Edit1", ("Provider Practice Record")) I have been running into lag alot. So my script has been missing input. I would like to be able to have it retry to put in "Provider Practice Record" if it misses. 
      I am assuming  a if then statement is what I need but I am not sure what ControlSend will return if it tries to send to Edit1 and it is not there. 
      Or do I need to just add a timeout? 
       
    • ChipConnJohn
      By ChipConnJohn
      Hello all,
      I'm trying to create a webscrape script using AutoIt.  A non-profit I'm involved with is using GroupSpaces.com and there is no notification to the group when someone posts a new topic to the forum.  I'm including IE.au3

      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <String.au3>
      ; Open Groupspaces.com
      Global $oIE = _IECreate ("http://www.groupspaces.com")
      _IENavigate($oIE,"http://groupspaces.com/MyCrazyTest/forum/")
      ;Grab all the links on the Forum page
      Global $oLinksCategoryPage = _IELinkGetCollection($oIE)
      For $oLink In $oLinksCategoryPage
          Local $oLinkHRef = $oLink.href
          If StringInStr($oLinkHRef,"forum_id") Then
              ;This is a category. Click into it.
              _IENavigate($oIE,$oLinkHRef)
      Sleep(3)
              ;Back out to Category List
              _IEAction($oIE,"back")
          EndIf
      Next
      _IEQuit($oIE)