Excel read filtered only

[Ebola57]

Hi Community

I am intending to read only the filtered cells of a sheet

At a second stage  I have to write back in this sheet, so I need to store somewhere (why not in outputed array from _Excel_RangeRead )

May you help me to proceed

Such code does not give me a way to identify the index to write back if I am write

#include <Excel.au3>

$oExcel = _Excel_Open ( False, False, False, True )
$oWorkbook = _Excel_BookOpen ( $oExcel, "Myfile" )
$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)
    _ArrayConcatenate($aResult, $aContent)
Next
_ArrayDisplay($aResult)

Regards

Edited September 4, 2020 by Ebola57

[water]

A member of the Areas collection is a Range object.
You could use the Address property to retrieve the address of the upper left cell in this Range and then the .Columns.Count and .Rows.Count properties for the Area to retrieve  the number of columns and rows to calculate the other addresses.

[Nine]

Here the approach I would use.  Instead of creating a single 2D array containing all the values of all the areas, I suggest you store the range object into the first column of an array, and the second column would contain its corresponding matrix.  That way you can have different size of area.  You can modify each matrix individually and rewrite them into the excel sheet easily by using its range object.

#include <Excel.au3>

$oExcel = _Excel_Open()
$oWorkBook = _Excel_BookAttach("65000.xls","FileName")

$oRange = $oWorkbook.ActiveSheet.range("A1:B5;C10:E15;D20:G30")
$oRange.select
Local $aResult[$oRange.Areas.count][2], $i = 0
For $oArea In $oRange.Areas
  ConsoleWrite ("Area " & $i & " = " & $oArea.rows.count & "/" & $oArea.columns.count & @CRLF)
  $aResult[$i][0] = $oArea
  $aResult[$i][1] = _Excel_RangeRead($oWorkbook, Default, $oArea)
  _ArrayDisplay($aResult[$i][1])
  $i += 1
Next

; Make some modifications to the areas
; Example : Double the values of first column

Local $aTmp
For $i = 0 to UBound($aResult)-1
  $aTmp = $aResult[$i][1]
  For $j = 0 to UBound($aTmp)-1
    $aTmp[$j][0] *= 2
  Next
  _Excel_RangeWrite($oWorkBook, Default, $aTmp, $aResult[$i][0])
Next

 

[Ebola57]

Thanks for your help, it helps me a lot

This is code I wrote to get what I need. I catch data from Excel, add a column where line appears then filter the lines I need

By the way, I touch as few the Excel file as possible ( I need it to be fast )

$oUsed_Range = $oWorkbook.ActiveSheet.UsedRange
$aData_Used_Range = _Excel_RangeRead ( $oWorkbook, Default, $oUsed_Range )
_ArrayColInsert ( $aData_Used_Range, 0 )
$aData_Used_Range [0] [0] = "Index"
For $iLine = 1 To UBound ( $aData_Used_Range) - 1
    $aData_Used_Range [$iLine] [0] = $iLine + 1
Next
$Nb_of_Columns = $oWorkbook.ActiveSheet.Usedrange.Columns.Count
$oVisible_Ranges = $oWorkbook.ActiveSheet.Usedrange.SpecialCells ( $xlCellTypeVisible )
Local $aData_Visible_Range [0] [$Nb_of_Columns]
For $oVisible_Range In $oVisible_Ranges.Areas
    $aContent = _Excel_RangeRead ( $oWorkbook, Default, $oVisible_Range )
    _ArrayConcatenate ( $aData_Visible_Range, $aContent )
Next
_ArrayColInsert ( $aData_Visible_Range, 0 )
$aData_Visible_Range [0] [0] = "Index"
For $iLine = 1 To UBound ( $aData_Visible_Range) - 1
    Local $iFound = _ArraySearch ( $aData_Used_Range, $aData_Visible_Range [$iLine] [1], 1, Default, Default, Default, Default, 1 )
    If Not @error Then $aData_Visible_Range [$iLine] [0] = $aData_Used_Range [$iFound] [0]
Next

 

Edited September 24, 2020 by Ebola57