HighlanderSword

Excel UDF RangeRead with a Filter Set

8 posts in this topic

#1 ·  Posted (edited)

Hello,

 

I have an Excel Document that has 147 columns, in Column 7 I have a filter set to limit the amount of records returned.

I need to read the results from the filtered set and only return 3 columns , as:au with only the filtered results being returned

How do you do this using rangeRead or another function ?

 

Edited by HighlanderSword

Share this post


Link to post
Share on other sites



What have you tried so far and what were the results?


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

 

Below are the ones I've been trying

 

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.SpecialCells($xlCellTypeVisible), 2)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.SpecialCells(12).columns("a:c"), 2)

 Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:Ax"), 2)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.sheet("Data").columns("a1:c5"), 2)

Share this post


Link to post
Share on other sites

At the moment _Excel_RangeRead stops at the first hidden line and only returns those records read so far.

 

 


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

As a workaround.
You could read the needed columns and then filter out the hidden rows:

#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Global $aRange[100][2], $iRows = 0
For $oRow In $oWorkbook.ActiveSheet.AutoFilter.Range.Rows ; Loop through the rows of the filtered range
    If $oRow.Hidden = False Then ; If row is visible process it
        $aRange[$iRows][0] = $oRow.Columns(1).Value ; Move value, formula of column 1 to the array
        $aRange[$iRows][1] = $oRow.Columns(2).Value ; Move value, formula of column 2 to the array
        $iRows = $iRows + 1
    EndIf
Next
ReDim $aRange[$iRows][2]
_ArrayDisplay($aRange)

 


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

ok,I will remove the filter and do a standard rangeread and the filter out from the array what I need to get

Share this post


Link to post
Share on other sites

No need to remove the filter.
My example grabs all rows from the filtered range and only grabs those rows which are not hidden.


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

thanks , that worked !!!

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