Sign in to follow this  
Followers 0
andomatic

Help with reading an Excel Sheet to an Array

3 posts in this topic

Hello.

I am trying to accomplish a (looks like) simple task. I need to read a column from an excel sheet into an array and then loop through the values to test against them. My workbook has multiple sheets. When I run my code:

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

Const $sKeyWordPath = "p:\autoitsrc\code\keywords.xls"; Excel file with search terms
Const $sTextFilePath = "p:\autoitsrc\bystate\AL"; State pdf to text conversions

Dim $sTextFileContents
Dim $sPaymentAmt
Dim $aFileList

; Open up the keywords and populate and array
$oExcel =_ExcelBookOpen($sKeyWordPath,0,"True")
_ExcelSheetActivate($oExcel, "duration")

$aArray = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($aArray, "Array using Default Parameters")

; Read in each text file and then loop the array until we get a hit
$aFileList = _FileListToArray($sTextFilePath, "*.txt")
If @error=1 Then
    MsgBox (0, "", "No Files\Folders Found.")
    Exit
EndIf

Global $aFileStrings[$aFileList[0] + 1][2] = [[$aFileList[0]]]

For $i = 1 To $aFileList[0]
    $sFile = FileRead($sTextFilePath & "\" & $aFileList[$i])
    ; Check for the keywords...
        For $kw = 1 to $aArray[0]
            msgbox(0,"Keywords...",$kw)
        Next
    ;msgbox(0,"File Results",$sFile)
Next

_ExcelBookClose($oExcel)

I get an error as below:

P:\AutoItSrc\code\GOLD\exceltest.au3 (33) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

For $kw = 1 to $aArray[0]

For $kw = 1 to ^ ERROR

->14:32:14 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 8.243

when I run the code. A kick in the correct direction is appreciated.

Thanks,

Andy

Share this post


Link to post
Share on other sites



_ExcelReadSheetToArray creates a two-dimensional array so you have to use $aArray[0][0] not just $aArray[0].


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, perfect. some times you overlook things 2 inches away.

Thanks Again,

Andy

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
Sign in to follow this  
Followers 0