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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

    • TrashBoat
      By TrashBoat
      So Im trying to make a simple 2d game and make some sort of collision detection so why not to make a 2 dimensional array but i have no clue how  to write it in multiple lines
      Global $map[5,5] = [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0] something like this but it doesn't work
    • Zein
      By Zein
      #include "..\Include\Array.au3" #include "..\Include\File.au3" #include "..\Include\AutoItConstants.au3" Local $aRetArray Local $sFilePath = "n.csv" _FileReadToArray($sFilePath, $aRetArray, ",") ; _FileReadToArray($sFilePath, $aRetArray, $FRTA_COUNT, ",") _ArrayDisplay($aRetArray, "Original", Default, 8) The above code shows two versions of _FileReadToArray and both don't work as expected.
      The first one doesn't use the comma as a delimiter. (so I get a single column array)  I tried adding "Default" between $aRetArray and "," then it told me it had an incorrect number of parameters. 

      I looked again at the documentation:
       
      #include <File.au3> _FileReadToArray ( $sFilePath, ByRef $vReturn [, $iFlags = $FRTA_COUNT [, $sDelimiter = ""]] )
      And I with or without the flags params I should be getting a 2D array due to my file being a csv. 
      I then tried a regular flag, $FRTA_COUNT, and it tells me that I'm using a variable $FRTA_COUNT while it's not declared. Tried putting in 1 instead and it told me again, incorrect number of params. 

       
    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
×