Klutz Posted March 7, 2018 Share Posted March 7, 2018 Hello! I am wanting to apply an advanced filter in Excel. I have about a 95% success rate using a 'high quality' blend of Sends and Sleeps. However, I'd like to follow best practice when possible. According to MSDN, the object/method is expression.AdvancedFilter(Action , CriteriaRange , CopyToRange , Unique). When trying to run my script, I get a console error: "E:\Testing001.au3" (49) : ==> The requested action with this object has failed.: $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3") $oWorkbook.ActiveSheet.Range("A1:H24")^ ERROR Here is my example code. Attached is the .au3 and example Excel file should you want to use it. The premise is to sort for students that are not assigned devices, or if they are whether or not the Tag or Serial Number is missing from the field. Since it's for 4th grade and up, I also want to exclude third grade. expandcollapse popup#include <Excel.au3> #include <MsgBoxConstants.au3> DirCreate(@ScriptDir & "\Backups") DirCreate(@ScriptDir & "\Edits_For_Uploads") Global $BackupDir = @ScriptDir & "\Backups" Global $EditDir = @ScriptDir & "\Edits_For_Uploads" Global $TimeStamp = @YEAR&@MON&@MDAY&'_'&@HOUR&@MIN&@SEC Global $SISBlankBackup = $TimeStamp & '_' & 'SISBlankBackup.xlsx' Global $SISBlankEdit = $TimeStamp & '_' & 'SISBlankEdit.xlsx' Global $SISAssignBackup = $TimeStamp & '_' & 'SISAssignBackup.xlsx' Global $SISAssignEdit = $TimeStamp & '_' & 'Has Assigned Devices.xlsx' Global $SISAssignEdit2 = @ScriptDir & '\ExcelExample.xlsx' SISAssignExcelFilter("IN", 2) Func SISAssignExcelFilter($vBuilding, $vStep = 1) ;~ Open Excel Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $SISAssignEdit2, True) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error opening workbook '" & $SISAssignEdit2 & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf Sleep(5000) ;~ Add Advanced Filter array Local $aFilterValues = ['=E2<>"03"', '=E2<>"03"', '=G2=""', '""', '""', '=H2=""'] _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[0], "J2", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[1], "J3", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[2], "K2", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[5], "L3", False) ;~ Apply Advanced Filter (Headers of filter array have to be blank...) $xlFilterInPlace = 'xlFilterInPlace' $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3") EndFunc Thank you! Link to comment Share on other sites More sharing options...
Klutz Posted March 7, 2018 Author Share Posted March 7, 2018 Forgot to add files and since I'm new, I can't edit! ExcelExample.xlsx Testing.au3 Link to comment Share on other sites More sharing options...
Juvigy Posted March 8, 2018 Share Posted March 8, 2018 Search the forum, there are examples. You can specifically check for my posts - i remember answering the same questions few months ago. Link to comment Share on other sites More sharing options...
water Posted March 8, 2018 Share Posted March 8, 2018 I modified the $xlfilterinplace constant and added a COM error handler: expandcollapse popup#include <Excel.au3> #include <MsgBoxConstants.au3> DirCreate(@ScriptDir & "\Backups") DirCreate(@ScriptDir & "\Edits_For_Uploads") Global $BackupDir = @ScriptDir & "\Backups" Global $EditDir = @ScriptDir & "\Edits_For_Uploads" Global $TimeStamp = @YEAR&@MON&@MDAY&'_'&@HOUR&@MIN&@SEC Global $SISBlankBackup = $TimeStamp & '_' & 'SISBlankBackup.xlsx' Global $SISBlankEdit = $TimeStamp & '_' & 'SISBlankEdit.xlsx' Global $SISAssignBackup = $TimeStamp & '_' & 'SISAssignBackup.xlsx' Global $SISAssignEdit = $TimeStamp & '_' & 'Has Assigned Devices.xlsx' Global $SISAssignEdit2 = @ScriptDir & '\ExcelExample.xlsx' Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") SISAssignExcelFilter("IN", 2) Func SISAssignExcelFilter($vBuilding, $vStep = 1) ;~ Open Excel Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $SISAssignEdit2, True) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error opening workbook '" & $SISAssignEdit2 & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ;~ Add Advanced Filter array Local $aFilterValues = ['=E2<>"03"', '=E2<>"03"', '=G2=""', '""', '""', '=H2=""'] _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[0], "J2", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[1], "J3", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[2], "K2", False) _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[5], "L3", False) ;~ Apply Advanced Filter (Headers of filter array have to be blank...) $xlFilterInPlace = 1 $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3") EndFunc ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError) ; Do anything here. ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _ @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _ @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _ @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _ @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _ @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _ @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _ @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _ @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _ @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc ;==>_ErrFunc Maybe your problem is decribed herehttps://support.office.com/en-us/article/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b in section: Using the equal sign to type text or a value 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...
Klutz Posted March 12, 2018 Author Share Posted March 12, 2018 Thanks for the responses! On 3/8/2018 at 10:31 AM, Juvigy said: Search the forum, there are examples. You can specifically check for my posts - i remember answering the same questions few months ago. Juvigy, I searched back to October searching for posts about Excel and I did not find anything that seemed to be about this. Sorry if I missed it! I know the Advanced filter I have in place works, because I can do it manually or send hot keys to get the job done. Send('{Alt}') Sleep(100) Send('{a}') Sleep(100) Send('{q}') Sleep(100) Send('{Tab}') Sleep(100) Send('$J$1:$L$3') Sleep(100) Send('{Enter}') Sleep(500) I guess my main question is can AutoIt process all COM's? With the error happening at the AdvancedFilter Method, either my syntax is wrong or I need to find another method to go about it. Below is the result of the COM error func Water added. Thanks for that! Testing.au3 (41) : ==> COM Error intercepted ! err.number is: 0x80020009 err.windescription: Exception occurred. err.description is: AdvancedFilter method of Range class failed err.source is: Microsoft Excel err.helpfile is: xlmain11.chm err.helpcontext is: 0 err.lastdllerror is: 0 err.scriptline is: 41 err.retcode is: 0x800A03EC Thanks! Link to comment Share on other sites More sharing options...
Klutz Posted March 12, 2018 Author Share Posted March 12, 2018 Found it! The Criteria argument needed everything. $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter(1, $oexcel.ActiveSheet.Range("$J$1:$L$3")) 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