[Solved] Excel RangeRead Help with sheet name selection

[AnonymousX]

Hello,

I'm struggling and not sure what I'm missing with how to use the _Excel_RangeRead function.

Based on: https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm

The second parameter should allow me to select different sheet names, however I can't seem to get it to work.

I was thinking something like this (see below) would be all I need to get an array for the sheet named as "test sheet". 

$array = _Excel_RangeRead($oWorkbook, "test sheet")

 

Can someone please help tell me what I'm missing here?

 

Thank you,

Edited February 24, 2021 by AnonymousX

[FrancescoDiMuro]

1 minute ago, AnonymousX said:

Can someone please help tell me what I'm missing here?

Error checking:

Spoiler

_Excel_RangeRead()
If @error Then 
    ConsoleWrite("_Excel_RangeRead() ERR: " & @error & @CRLF)
Else
    ; Continue with the execution of your script
EndIf

 

Put some error checking in your script and see what happens.
 

[AnonymousX]

No errors occur so not sure what to check.

 

I have _Arraydisplay($array) being used and it just seems to skip that line in the code. So I know something isn't working but I'm not sure what. I tried testing with other sheet names and no luck either.

 

Also thought it funny that I tried searching for how to check errors and your forum from 4 years ago came up. 

 

Edited February 24, 2021 by AnonymousX

[FrancescoDiMuro]

@AnonymousX
Could you please post the entire script?

[AnonymousX]

I created a test excel workbook and it actually worked as intended for me...

but when I go back to try on the sheet I actually want to pull from it skips the array display, and has message box for done popup. I'm wondering if it has to do with all the objects on the sheet, things like checkboxes, hidden combo boxes, buttons, etc. As on a new excel sheet that just has a couple data entries it worked perfectly....

Seems like something in my excel sheet is messing it up. I'll probably have to tackle that one on my own with more troubleshooting.

#include <Excel.au3>

$Folderpath = @ScriptDir &"\test.xlsm"

Local $oExcel = _Excel_Open()
local $oWorkbook = _Excel_BookOpen($oExcel, $Folderpath)

$array = _Excel_RangeRead($oWorkbook,"Data")

_ArrayDisplay($array)

MsgBox(0,0,"done")

 

Edited February 24, 2021 by AnonymousX

[FrancescoDiMuro]

5 minutes ago, AnonymousX said:

I'll probably have to tackle that one on my own with more troubleshooting.

See ya later

[water]

Try:

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

$sFolderpath = @ScriptDir & "\test.xlsm"

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error when starting Excel. @error=" & @error & ", @extended=" & @extended)

Local $oWorkbook = _Excel_BookOpen($oExcel, $sFolderpath)
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error when opening workbook. @error=" & @error & ", @extended=" & @extended)

Local $aData = _Excel_RangeRead($oWorkbook, "Data")
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error reading data - version 1. @error=" & @error & ", @extended=" & @extended)
_ArrayDisplay($aData, "_Excel_RangeRead - Version 1")

Local $aData = _Excel_RangeRead($oWorkbook, "Data", Default, 1, True)
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error reading data - version 2. @error=" & @error & ", @extended=" & @extended)
_ArrayDisplay($aData, "_Excel_RangeRead - Version 2")

MsgBox($MB_ICONINFORMATION, "Information", "Done")

Edited February 24, 2021 by water

[AnonymousX]

@water

Thanks Water, I gave that a go and was able to trigger an error. The problem is I don't understand how to use this new information on the error though....

I tried reading https://www.autoitscript.com/autoit3/docs/functions/SetError.htm but that didn't help me understand.

 

 

 

So I commented out the version 1 to see what would happen with version 2 and it worked! So can't say I understand, but thanks so much for helping me get to a solution.

Edited February 24, 2021 by AnonymousX

[water]

You did not get an error for "Version 2" and the read data was correctly displayed?

If yes, then the error was caused by the data in your workbook. Excel has same limitations I have described in the wiki.
To solve the problem use the "Version 2" line of code. This tells _Excel_RangeRead to ignore the Excel transpose function and use the internal function of the UDF.

 

[AnonymousX]

Yes no error from Version 2, it just worked!

So what you're saying is that excel is coded poorly and that your UDF fixes that? Why would this not be the default if it works better?

[water]

It's not the default because usually Excel does a good job and does it FAST.
The internal function used to transpose the array is _ArrayTranspose from the Array UDF. It's much slower because it has to transpose the read data cell by cell.

[AnonymousX]

Thanks for the explanation!

[water]

[AnonymousX]

I know this question is impossible to answer with out full context but in general what would your opinion be for speed purposes: would you think it be faster to run range read function once for a full sheet, or to run it a hand full of times to get specific cells that I'm after? 

[water]

It depends. On the size of the worksheet and the number of cells you need to process.
Most of the time reading the full worksheet and then processing the returned array is faster.

[AnonymousX]

Thanks for the response