_Excel_RangeRead How to read all cells in a single column

[noellarkin]

_Excel_RangeRead($ExcelWorkBook, $Sheet, "A1:A3", 1)

I'm aware of how to use sheet ranges to delimit Range Read.

In the help file, I've also come across this:

_Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

How can I edit _Excel_RangeRead($ExcelWorkBook, $sheet, "A1:A3", 1) so it reads all cells of column A (similar to the help file example) but without using the workbook.activesheet.usedrange object oriented format/syntax?

[Danp2]

Why not use the recommended solution of workbook.activesheet.usedrange?

[noellarkin]

I'm iterating through sheets using a For/Next loop, so I need the sheet to be a variable, as in:
 

Local $Sheet = ""
Local $Array
For i = 0 To UBound($SheetList) - 1
    $Sheet = $SheetList[$i]
    $Array = _Excel_RangeRead($ExcelWorkBook, $Sheet, "A1:A3", 1)
    _ArrayDisplay($Array)
Next

 

[Danp2]

Have you tried like this?

Local $Sheet = ""
Local $Array
For i = 0 To UBound($SheetList) - 1
    $Sheet = $SheetList[$i]
    $Array = _Excel_RangeRead($ExcelWorkBook, $Sheet, $Sheet.Usedrange.Columns("A:A"), 1)
    _ArrayDisplay($Array)
Next

[noellarkin]

Thanks, I tried it, but I'm getting the error:

Error: Variable must be of type "Object".

 

[noellarkin]

I tried this, too, but the script just stalls when I run it:

_Excel_RangeRead($ExcelWorkBook, $Sheet, "A:A", 1)

 

[Danp2]

Where does $SheetList come from?

[noellarkin]

Sheet list is a 1D array of some of the sheets in the workbook that I'm iterating through in the function.

Something like:

Local $SheetList[4] = ["INFO", "CHECKLIST", "NAMELIST", "EMAILS"]

where INFO, CHECKLIST, NAMELIST, EMAILS are sheets in the xlsx file.

 

[Danp2]

Ok... Then something like this should work --

$Array = _Excel_RangeRead($ExcelWorkBook, $Sheet, $ExcelWorkBook.worksheets($Sheet).Usedrange.Columns("A:A"), 1)

 

Edited March 27, 2022 by Danp2
Missing $

[water]

A $ is missing, should be:

$Array = _Excel_RangeRead($ExcelWorkBook, $Sheet, $ExcelWorkBook.Worksheets($Sheet).Usedrange.Columns("A:A"), 1)

 

[noellarkin]

Thanks. This works.

[ThomasBennett]

On 3/27/2022 at 10:12 AM, Danp2 said:

Why not use the recommended solution of workbook.activesheet.usedrange?

...not meaning to resurrect an old post; mod please have mercy.

Look at _Excel_RangeRead Example 3:
https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm

[JLogan3o13]

55 minutes ago, ThomasBennett said:

...not meaning to resurrect an old post

And yet that is exactly what you did, a year after the OP accepted the resolution from the guy who wrote the example you're pointing him to.

Edited June 23, 2023 by JLogan3o13