Jump to content
Eminence

Accessing Data in an array from Excel

Recommended Posts

Eminence

Hello,

Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?

Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open(False)
    If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended)

; Open Excel Woorkbook and return object
Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True)
    If @error Then
        MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

Local $aResult = _Excel_RangeRead($oWorkbook)
    ; Error Trapping
    If @error Then
        MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
_ArrayDisplay($aResult)

My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 

 

Any help is appreciated. Thanks in advance.

Share this post


Link to post
Share on other sites
water

Quite easy ;)

$sResult = _Excel_RangeRead($oWorkbook, Default, "A7")

 


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
JLogan3o13

@Eminence welcome to the forum, but please pay attention to where you post in the future. The DEV forum very clearly states:

 

Quote

Do not create AutoIt-related topics here, use AutoIt General Help and Support

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Eminence
20 hours ago, water said:

Quite easy ;)

$sResult = _Excel_RangeRead($oWorkbook, Default, "A7")

 

Oh lol. Thanks! :D

19 hours ago, JLogan3o13 said:

@Eminence welcome to the forum, but please pay attention to where you post in the future. The DEV forum very clearly states:

 

 

My bad. Will do next time. Thank you! 

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

    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
    • Iceburg
      By Iceburg
      Hi everyone, I'm at best a noobie.  I have read through the Array helps, and specifically the 2D array help file, and I'm struggling to get my code working.
      I have an array that is read from a file, thats working great.  I'm trying to do some math on the array, so I can find the largest, average, lowest, day over day change %, etc.
      The array read working fine, I get 43 lines, line 0 is 44, and then I get data that looks like
      0519 $10,000
      0520 $10,001
      0521 $10,002
      The data in this array is a single 1D array, breaking it out into 2 columns so I can do the math is what I can get to happen.  
      How do I reference the array to store this data?  Second, how do I assign this data to the appropriate row/column?
      Thanks in advance.
      Dim $all_cash_amounts[UBound($aInput)][2] Dim $max_amount_in_account Dim $min_amount_in_account _FileReadToArray($LC_Check_file_path, $aInput) _ArrayDisplay($aInput) local $date = StringRegExp($aInput[1], "(\d\d\d\d)", 1) local $cash = StringRegExp($aInput[1], "\d+\s(-?[0-9\.\,]+)", 1) ConsoleWrite("Date is: " & $date & @CRLF) For $i = 1 To UBound($aInput)-1     $date = StringRegExp($aInput[$i], "(\d\d\d\d)", 1)     $all_cash_amounts[$i][2] = $date[$i][0], $cash[$i][1]      Next _ArrayDisplay($all_cash_amounts)  
    • DynamicRookie
      By DynamicRookie
      Hey there!
       
      Is there a way to detect movement only for 1 specific window?

      HWnd is not really working, it still detects movement in all the windows within the region

      This is my script:
       
      ;Checksum actual HotKeySet("{f2}", 'exit_') MsgBox(0, '', '0') Beep(500, 2000) $Hwnd = WinActive('') while 1 $Checksum = PixelChecksum(211, 650, 1094, 914, 1, $Hwnd) If PixelChecksum(211, 650, 1094, 914, 1, $Hwnd) <> $Checksum Then For $i = 1 To 100 Beep(1000, 50) Next MsgBox(0, '', 'New message!', 1) EndIf WEnd Func exit_() Beep(500, 1000) Exit 0 EndFunc I just want to check whenever a message comes because the app i use doesn't give notifications properly.

      Any help is really appreciated!
    • OldGuyWalking
      By OldGuyWalking
      Given an array with multiple columns that is displayed in a listview,
       ===> What is the fastest/most efficient way to create and manage multiple filters and display results in ListView.
      I have a text file that loads into a listview that has string, numeric, and date columns.  The main file contains about 5100 rows. It's loaded into an array and (in this ListView) it's pre-filtered to display a range of rows based on a start and end date.  On the form I have menu options for various filters. (see below).
      I have options to filter on an "Air Date" column (=Today, >=Today, <=Today) and on a numeric field that is either 1 or 0 that indicate Active or Ended.

      For each filter option I have a prebuilt array that holds a subset of the main array based on a single filter.  For the list above I have the main Array and 5 additional arrays.  None of the arrays are updated since this is for "view only" purposes.  This is a short list and I could have done the filtering "live" but I have several of these forms and so kept the same functionality in each. I have another ListView that displays the complete 5100 row list with 3 filters that, when building the filters live was considerably slower than using prebuilt arrays.
      If I want to expand past simple single column filtering, using an array for each filter becomes cumbersome especially if I want to combine filters using AND & OR.
      The text file I'm working with has 16 columns. If I setup filters for 4 columns and include AND / OR capability that would require prebuilding 24 arrays to cover the various combinations.
      If using the slower method of building a filtered array in real time each time a different filter is selected is the only way to go with this then I'll live with it. It is less overhead. .
      Below is the code I'm currently using to "filter" an array.  My next change was going to add AND / OR functionality (see the info above the header for where I was going with this) .
      ; Description ...: Delete rows from an array and only keep rows that meet the crtieria of identified columns. ; ; Next Change: Add AND/OR to combine filters. Use array to hold multiple criteria and values? ; ; Local $aCriteria[][] = [["",$iColNbr1, $sOperator1, $vValue1], ["AND",$iColNbr2, $sOperator2, $vValue2], ["OR",$iColNbr3, $sOperator3, $vValue3]] ; The first set of criteria ["", $iColNbr1, $sOperator1, $vValue1] must start with a "". ; If anything is entered in that first parameter it will be ignored. ; If the first parameter in any additional criteria set is left blank, or it is not OR, it will default to AND. ; If $aArray is 1 dimension with more than one set of criteria, only the first set will be used. ; Any criteria that uses a column that is less than 0 or higher than the total number of columns in the array will return an error. ; ; Recognized data types for this function are: S (String), D (Date), N (Number). ; ; Recognized Operators are: "EQ", "NEQ", "IN", "GT", "GE", "LT", "LE", "BETWEEN". ; ****** Not all operators work with all data types. ; #FUNCTION# ==================================================================================================================== ; Name ..........: _ArrayFilter ; Description ...: Delete rows from an array and only keep rows that meet the crtieria of identified columns. ; Syntax ........: _ArrayFilter(Byref $aArray[, $iCol = 0[, $sOperator = "EQ"[, $vValue = ""[, $iOptionBase = 0]]]]) ; Parameters ....: $aArray - Array being filtered. ; $iCol - [optional] Column to filter. Default is 0. ; $sOperator - [optional] Operator. Default is "EQ". ; $vValue - [optional] Criteria to compare the column/row value against. ; $iOptionBase - [optional] Starting row. Default is 0. ; Return values .: None ; Author ........: OldGuyWalking ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _ArrayFilter(ByRef $aArray, $iCol = 0, $sOperator = "EQ", $vValue = "", $iOptionBase = 0) Local $hFunc = _ArrayFilter $vValue = StringStripWS($vValue, 3) If $vValue = "[Today]" Then $vValue = _NowCalcDate() EndIf Local $sMsg Local $sMsgHdr Local $n1 Local $sDeleteIndex Local $aDeleteIndex Local $iCnt = 0 Local $iRows Local $iColMax Local $iDim Local $sData Local $sVType Local $sDType Local $LBound Local $iDiff If $iOptionBase <> 0 Then $iOptionBase = 1 EndIf If _IsValueEmpty($aArray) Then Return SetError(1, 0, "") EndIf $iDim = UBound($aArray, $UBOUND_DIMENSIONS) If $iDim = 1 Then If $iCol <> 0 Then $iCol = 0 EndIf EndIf If $iDim = 2 Then $iColMax = UBound($aArray, $UBOUND_COLUMNS) - 1 If $iCol > $iColMax Or $iCol < 0 Then Return SetError(1, 0, "") EndIf EndIf If Not _IsBetween($iDim, 1, 2) Then ;############### MSG2 - START ############### $sMsgHdr = FuncName($hFunc) & " :Line: " & @ScriptLineNumber & " :Error= " & @error $sMsg = "Invalid Dimensioned Array. Must be a 1 or 2 dimensional array." MsgBox(0, $sMsgHdr, $sMsg) Return SetError(1, 0, "") ;############### MSG2 - END ############### EndIf ; Identify what the value is ; If it is not a String, Int, Number, or Date then skip. Select Case _DateIsValid($vValue) = 1 $sVType = "D" Case IsNumber($vValue) = 1 $sVType = "N" Case IsString($vValue) = 1 $sVType = "S" Case Else ;############### MSG2 - START ############### $sMsgHdr = FuncName($hFunc) & " :Line: " & @ScriptLineNumber & " :Error= " & @error $sMsg = "Comparison value must be a " & @CRLF & _ "1. Date in YYYY/MM/DD format " & @CRLF & _ "2. A string " & @CRLF & _ "3. A number " & @CRLF MsgBox(0, $sMsgHdr, $sMsg) Return SetError(1, 0, "") ;############### MSG2 - END ############### EndSelect $iCnt = 0 For $n1 = UBound($aArray) - 1 To $iOptionBase Step -1 If $iDim = 1 Then $sData = StringStripWS($aArray[$n1], 3) ElseIf $iDim = 2 Then $sData = StringStripWS($aArray[$n1][$iCol], 3) EndIf Select Case _DateIsValid($sData) = 1 $sDType = "D" Case IsNumber($sData) = 1 $sDType = "N" Case IsString($sData) = 1 $sDType = "S" Case Else $sDType = "U" EndSelect If _IsValueEmpty($sData) Then $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop ; $sDType = $sVType EndIf If Not _IsValueEmpty($sData) And $sDType <> $sVType Then $sDeleteIndex = $sDeleteIndex & $n1 & "," $iCnt += 1 ContinueLoop EndIf Select Case $sOperator = "EQ" Switch $sDType Case "D" $iDiff = _DateDiff("D", $vValue, $sData) If $iDiff = 0 Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "S" If $sData = $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "N" If $sData = $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "NEQ" Switch $sDType Case "D" If $sData <> $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "S" If $sData <> $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "N" If $sData <> $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "IN" Switch $sDType Case "S" If StringInStr($sData, $vValue) Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "GT" Switch $sDType Case "N" If $sData > $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "D" $iDiff = _DateDiff("D", $vValue, $sData) If $iDiff > 0 Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "GE" Switch $sDType Case "N" If $sData >= $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "D" $iDiff = _DateDiff("D", $vValue, $sData) If $iDiff >= 0 Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "LT" Switch $sDType Case "N" If $sData < $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "D" $iDiff = _DateDiff("D", $vValue, $sData) If $iDiff < 0 Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch Case $sOperator = "LE" Switch $sDType Case "N" If $sData <= $vValue Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop Case "D" $iDiff = _DateDiff("D", $vValue, $sData) If $iDiff <= 0 Then ContinueLoop EndIf $sDeleteIndex &= $n1 & "," $iCnt += 1 ContinueLoop EndSwitch EndSelect Next If $iCnt > 0 Then _DeleteArrayRows($aArray, $sDeleteIndex) EndIf EndFunc ;==>_ArrayFilter Thanks in advance.
      OldGuyWalking
    • PramodR
      By PramodR
      Can you please see whats wrong im doing in the below code? i can read few registry entry but not all for eg, i am able to get value of "Shell" Key from the registry path  but not AutoAdminLogon which has the value of "1", is there any change i have to do to read numeric values?
       Global  $shello = RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon","Shell" )
       ConsoleWrite("This is Hello"& $shello)
      above command returns proper value , but same command with AutoAdminLogon returns null as shown in below ? infact registry has the value of 1.
       Global  $shello = RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon","AutoAdminLogon" )
       ConsoleWrite("This is Hello"& $shello)
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.