HighlanderSword Posted August 11, 2015 Share Posted August 11, 2015 (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 returnedHow do you do this using rangeRead or another function ? Edited August 11, 2015 by HighlanderSword Link to comment Share on other sites More sharing options...
water Posted August 11, 2015 Share Posted August 11, 2015 What have you tried so far and what were the results? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
HighlanderSword Posted August 11, 2015 Author Share Posted August 11, 2015 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) Link to comment Share on other sites More sharing options...
water Posted August 11, 2015 Share Posted August 11, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted August 11, 2015 Share Posted August 11, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
HighlanderSword Posted August 11, 2015 Author Share Posted August 11, 2015 ok,I will remove the filter and do a standard rangeread and the filter out from the array what I need to get Link to comment Share on other sites More sharing options...
water Posted August 11, 2015 Share Posted August 11, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
HighlanderSword Posted August 11, 2015 Author Share Posted August 11, 2015 thanks , that worked !!! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now