HighlanderSword Posted August 11, 2015 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
water Posted August 11, 2015 Posted August 11, 2015 What have you tried so far and what were the results? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
HighlanderSword Posted August 11, 2015 Author 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)
water Posted August 11, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
water Posted August 11, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
HighlanderSword Posted August 11, 2015 Author 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
water Posted August 11, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
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