claycrosby123

Populating Arrays from Excel

6 posts in this topic

Hi all,
 
I am working on a data entry utility to enter large amounts of data into a piece of software. My data lives in a series of excel spreadsheets each with 6 columns and 100 rows (the software into which I'm entering my data has a limit of 100 rows per entry).
 
I'm having a hard time getting my arrays to populate with data from Excel. Here's what I've got set up:

;; Populate arrays with CDM charge info from excel doc
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel,@DesktopDir & "\AutoItTest.xlsx")
Sleep(2000)

Local $aProcedure = _Excel_RangeRead($oExcel,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"),1)

I then repeat this for each of my other 5 columns of data.

Based on the information in Help, I take this to populate my $aProcedure array with all of the contents in column A.

Please help! Thank you!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

This works just fine for me:

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

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls")

Local $aArray = _Excel_RangeRead($oWorkbook, Default, "A:A")
    _ArrayDisplay($aArray)

Edit: Sorry, I missed that you're trying to get used range only...

Modified, this works for me:

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

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls")

Local $aArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("A:A"))
    _ArrayDisplay($aArray)



Edited by JLogan3o13

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

Share this post


Link to post
Share on other sites

Parameter 1 for function_Excel_RangeRead should be the workbook object, not the Excel application object.

Means: Replace $oExcel with $oWorkbook


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

Missed that completely >_<


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

Share this post


Link to post
Share on other sites

Thank you very much! Simple fix  :bye:

Share this post


Link to post
Share on other sites

:)


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

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