Loading Excel Range to Dictionary - Error

[FernanS1]

Hi Guys,

I'm getting an error and I can't figure out why. Basically my script grabs 2 columns of data at a time from an excel file (attached). The problem is it is successfully grabbing those columns, storing it into an array, and loading it into a dictionary for all but one range of data. Please see my code below:

expandcollapse popup

; This function loads the CU definitons and labels from a local excel file into a Dictionary Object, ready to be used when parsing through the user selections
Func LoadDictionary()
   Local $oExcel = _Excel_Open() ; Open an instance of excel
   Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\CUDictionary.xls") ; Open the local CU Dictionary Excel Book
   Global $oDictionary = ObjCreate("Scripting.Dictionary") ; Create a Dictionary object to store keys and values

   $oExcel.Visible = 0 ; Make the excel file not visible

   Local $aExcelPoleCUGroupArray = $oExcel.Activesheet.Range("A1:B7").Value ; Grab the Pole CU Data from Excel and store it into a Local array

   for $i = 0 to 6 Step +1
      $oDictionary.Add($aExcelPoleCUGroupArray[0][$i], $aExcelPoleCUGroupArray[1][$i]) ; Throw the Pole CU key & values into the Dictionary
   Next

   Local $aExcelAnchorCUGroupArray = $oExcel.Activesheet.Range("D1:E3").Value ; Grab the Anchor CU Data from Excel and store it into a Local array

   for $i = 0 to 2 Step +1
      $oDictionary.Add($aExcelAnchorCUGroupArray[0][$i], $aExcelAnchorCUGroupArray[1][$i]) ; Throw the Anchor CU key & values into the Dictionary
   Next

   Local $aExcelGuyCUGroupArray = $oExcel.Activesheet.Range("G1:H7").Value ; Grab the Guy CU Data from Excel and store it into a Local array

   for $i = 0 to 6 Step +1
      $oDictionary.Add($aExcelGuyCUGroupArray[0][$i], $aExcelGuyCUGroupArray[1][$i]) ; Throw the Guy CU key & values into the Dictionary
   Next

   Local $aExcelRiserCUGroupArray = $oExcel.Activesheet.Range("J1:K7").Value ; Grab the Riser CU Data from Excel and store it into a Local array

   for $i = 0 to 6 Step +1
      $oDictionary.Add($aExcelRiserCUGroupArray[0][$i], $aExcelRiserCUGroupArray[1][$i]) ; Throw the Riser CU key & values into the Dictionary
   Next

   Local $aExcelGroundCUGroupArray = $oExcel.Activesheet.Range("M1:N5").Value ; Grab the Ground CU Data from Excel and store it into a Local array

   for $i = 0 to 4 Step +1
      $oDictionary.Add($aExcelGroundCUGroupArray[0][$i], $aExcelGroundCUGroupArray[1][$i]) ; Throw the Ground CU key & values into the Dictionary
   Next

   Local $aExcelBlackburnCUGroupArray = $oExcel.Activesheet.Range("P1:Q8").Value ; Grab the Blackburn CU Data from Excel and store it into a Local array

   for $i = 0 to 7 Step +1
      $oDictionary.Add($aExcelBlackburnCUGroupArray[0][$i], $aExcelBlackburnCUGroupArray[1][$i]) ; Throw the Blackburn CU key & values into the Dictionary
   Next

   Local $aExcelLaborCUGroupArray = $oExcel.Activesheet.Range("S1:T2").Value ; Grab the Labor CU Data from Excel and store it into a Local array

   for $i = 0 to 1 Step +1
      $oDictionary.Add($aExcelLaborCUGroupArray[0][$i], $aExcelLaborCUGroupArray[1][$i]) ; Throw the Labor CU key & values into the Dictionary
   Next

   Local $aExcelWireOHCUGroupArray = $oExcel.Activesheet.Range("V1:W10").Value  ; Grab the OH Wire CU Data from Excel and store it into a Local array

   for $i = 0 to 9 Step +1
      $oDictionary.Add($aExcelWireOHCUGroupArray[0][$i], $aExcelWireOHCUGroupArray[1][$i]) ; Throw the OH Wire CU key & values into the Dictionary
   Next

   Local $aExcelWireUGCUGroupArray = $oExcel.Activesheet.Range("Y1:Z16").Value ; Grab the UG Wire CU Data from Excel and store it into a Local array
   
   for $i = 0 to 15 Step +1
      $oDictionary.Add($aExcelWireUGCUGroupArray[0][$i], $aExcelWireUGCUGroupArray[0][$i]) ; Throw the UG Wire CU key & values into the Dictionary
   Next

   Local $aExcelXFMRCUGroupArray = $oExcel.Activesheet.Range("AC1:AD30").Value ; Grab the XFMR CU Data from Excel and store it into a Local array

   for $i = 0 to 29 Step +1
      $oDictionary.Add($aExcelXFMRCUGroupArray[0][$i], $aExcelXFMRCUGroupArray[1][$i]) ; Throw the XFMR CU key & values into the Dictionary
   Next

   $oExcel.quit


EndFunc

And the error is displaying

"J:\DIST_ENG\Fernandes\Service Engineering Tools\AutoIT\Scripts\WAMS Automation\Source Code\ServiceAuto v1\v1.2\TEST.au3" (1182) : ==> The requested action with this object has failed.:
$oDictionary.Add($aExcelWireUGCUGroupArray[0][$i], $aExcelWireUGCUGroupArray[0][$i])
$oDictionary^ ERROR

 

I have a limited programming background (as it probably shows). I'm just trying to get started. Any push in the right direction (with debugging this error) would be fantastic.

Thanks!

Shawn

 

CUDictionary.xls

[pixelsearch]

Hi FernanS1

It seems very tricky. I could run your script without any error after having modified the data in Excel (column V), replacing all "0" with "O", maybe AutoIt didn't like the 1/0 ("division by zero" ?) found in the beginning of some cells. Here is the V column I used :

Also I modified 1 line of your code, but it wasn't the cause of the error :

$oDictionary.Add($aExcelWireUGCUGroupArray[0][$i], $aExcelWireUGCUGroupArray[1][$i] )

What is strange is : why modifying column V seems to fix the issue, when column Y also contains "1/0" in its cells and is part of the range creating your error ?

Also the error seems erratic : as soon as you modify some cells (starting with numbers) in the Excel file, you may find the same error appears in another column. Well... it's a start, good luck for the future as it took me a looong time for testing all this.

PS: to run FernanS1's script, download his Excel file and add this at the beginning of the script :

#include <Excel.au3>
LoadDictionary()

Good luck mate

Edited August 11, 2018 by pixelsearch

[water]

Add a COM error handler to your script for more detailed error description. See ObjEvent in the help file. 

[pixelsearch]

This should help FernanS1. After reading Water's great comment, I tried for the 1st time to use a COM error function (i'm totally newbie with COM) while simplifying FernanS1's script during debug time, This is the simplified script :

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>
LoadDictionary()

Func LoadDictionary()
   Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

   Local $oExcel = _Excel_Open()
   Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\CUDictionary.xls")
   Global $oDictionary = ObjCreate("Scripting.Dictionary")

   Local $vw = $oExcel.Activesheet.Range("V1:W10").Value
   For $i = 0 to 9
      $oDictionary.Add($vw[0][$i], $vw[1][$i])
   Next

   Local $yz = $oExcel.Activesheet.Range("Y1:Z16").Value
   For $i = 0 to 15
      $oDictionary.Add($yz[0][$i], $yz[1][$i]) ; THIS IS LINE 19
   Next

   $oExcel.quit
EndFunc

Func _ErrFunc($oError)
    MsgBox($MB_SYSTEMMODAL, "COM Error intercepted !", _
            "script name :" & @TAB & @ScriptName & @CRLF & _
            "err.scriptline : " & @TAB & $oError.scriptline & @CRLF & _
            "err.number : " & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            "err.windescription :" & @TAB & StringStripWS($oError.windescription, 1+2) & @CRLF & _
            "err.description : " & @TAB & $oError.description & @CRLF & _
            "err.source : " & @TAB & @TAB & $oError.source & @CRLF & _
            "err.helpfile : " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext : " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror : " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF)
EndFunc   ; ==> _ErrFunc

When this script is run, even without any @error test in the script (which is strange), the _ErrFunc will be activated and its MsgBox() will appear exactly 10 times with the same content, so we'll have to press Ok 10 times, here is the COM error :

A google check on the error return code shows this :

VBScript Error Codes :
(0x800A01C9) "This key already associated with an element of this collection"

I indicated in the script which line is line 19 (the error line), it corresponds to the update of the dictionary with data taken from columns Y&Z

So it seems that the values found 10 times in column V were correctly added to the dictionary, but when the script tries to add columns Y&Z, it cannot add them because 10 cells from column Y are exactly the same as 10 cells from column V

If you want to run the script, you'll have to download FernanS1's excel file (attached in his 1st message)

Good luck FernanS1 for the future and thanks Water for the good advice, I won't forget it

Edited August 11, 2018 by pixelsearch
added StringStripWS() in _ErrFunc() + moved err.scriptline just under script name

[water]

[FernanS1]

Got it! I also never knew about using the COM error function for debugging! Thanks both of you for helping me sort this out!!! Very much appreciated.