Excel - Filtered content to Array

[maniootek]

I am trying to create an array from a filtered Excel sheet.

I found this piece of code from here

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    _ArrayConcatenate($aResult, $aContent)
Next

and I created the example script

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;~ Local $oWorkbook = _Excel_BookOpen($oExcel, @AppDataDir & "\Extras\_Excel1.xls", True)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

Local $aShow[] = ["20", "40", "60"]
_Excel_FilterSet($oWorkbook, Default, Default, 2, $aShow, $xlFilterValues)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 4", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.Columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    _ArrayConcatenate($aResult, $aContent)
Next
_ArrayDisplay($aContent)

and it does not extract all filtered content (3 rows) but only 1 row

(I wanted to add screen shot with result but it says I can only upload file with max 3.32kB size :s )

here's anothere example which works fine (all rows are extracted to the array)

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;~ Local $oWorkbook = _Excel_BookOpen($oExcel, @AppDataDir & "\Extras\_Excel1.xls", True)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

_Excel_FilterSet($oWorkbook, Default, "A1:E30", 1, ">20", 1, "<40")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.Columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    _ArrayConcatenate($aResult, $aContent)
Next
_ArrayDisplay($aContent)

 

Edited January 31, 2022 by maniootek

[big_daddy]

You are displaying the array that contains the content of each area. Try displaying the array that this is concatenated with.

_ArrayDisplay($aResult)

[maniootek]

6 minutes ago, big_daddy said:

You are displaying the array that contains the content of each area. Try displaying the array that this is concatenated with.

_ArrayDisplay($aResult)

Oh come on! What a shame! So sorry... It's to much of programming for today for me.

Anyway, why am I limited to attach only 3.32kB file?

[big_daddy]

It's common to have a fresh set of eyes promptly notice the issue. No worries.

As for the attachment limit, check you attachment quota here.

[maniootek]

9 minutes ago, big_daddy said:

It's common to have a fresh set of eyes promptly notice the issue. No worries.

As for the attachment limit, check you attachment quota here.

Quote

You have used 8 MB of your 8 MB attachment limit.

is it possible to extend the limit? What should I do?

[big_daddy]

The attachment limit is set by Jon. I know that MVPs, Mods, and Devs all have increased limits. My suggestion would be to use Google Drive or some other free online storage and link to it from here.

[maniootek]

3 hours ago, big_daddy said:

My suggestion would be to use Google Drive or some other free online storage and link to it from here.

Google Drive will not work if someone is not logged in with google account.

This is also very inconvenient for me and others who read my post to upload to other webiste and then put link to the post. Some users might be afraid to click to the links.

For user like me, who use this forum for 13 years already I think is unfair to leave me only 8MB attachment limitation.

Anyway, the code I posted before (from AutoIt wiki)

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    _ArrayConcatenate($aResult, $aContent)
Next

I think second line should be changed:

Local $aResult[0][$oRange.columns.Count], $aContent

 because everytime $aResult has empty first row. @water

 

 

[big_daddy]

2 hours ago, maniootek said:

Google Drive will not work if someone is not logged in with google account.

This is also very inconvenient for me and others who read my post to upload to other webiste and then put link to the post. Some users might be afraid to click to the links.

For user like me, who use this forum for 13 years already I think is unfair to leave me only 8MB attachment limitation.

I understand your frustration. I also understand that webhosting only includes limited storage space.

2 hours ago, maniootek said:

I think second line should be changed:

Local $aResult[0][$oRange.columns.Count], $aContent

 because everytime $aResult has empty first row.

The referenced line is declaring the array and it can't be declared with zero elements. I'd assume that _Excel_RangeRead is returning an empty row or the row count as the first array element.

[water]

_Excel_RangeRead always returns a "a zero-based array for a range of cells."

AutoIt now allows to create arrays with 0 elements. Depends on the version you are running.
This works for AutoIt 3.3.14.5:

#include <Array.au3>
Global $aTest[0][2]
_ArrayDisplay($aTest)