13lack13lade Posted December 6, 2013 Posted December 6, 2013 (edited) Hey Guys! I need a little help with changing an excel filter, into a check box GUI the easiest way possible. As you can see in the picture there is a list of criteria with a Y or N (checked or unchecked) in order to give me the correct code in column A. What is the easiest way to do this so i have the check boxes for the Y/N Criteria and just a label i guess that changes depending on the result of the criteria. (basically so users don't have to filter an excel document anymore) Thanks guys! Edited December 6, 2013 by 13lack13lade
13lack13lade Posted December 6, 2013 Author Posted December 6, 2013 local $SheetName = "Sheet1" local $total = $oExcel.Worksheets($SheetName).UsedRange.Columns("A:A").Cells.Count$oExcel.Columns("A:A").Select$oExcel.Selection.Autofilter("1",'=')$oExcel.Rows("2:" & $total).Delete ; change 2: to 1: if you dont want to keep the first row (headers) Stolen from another post - Best way would be to set up with the above code and the check box linked to the filter with an if statement, if checkbox checked autofilter = "Y" else autofilter = "N" and then have a button you click will will just return the only visable cell in the excel document after filter is applied? Or am i looking at it from the wrong angle?
13lack13lade Posted December 11, 2013 Author Posted December 11, 2013 is something like this along the right lines or is there an easier/better way of doing it? expandcollapse popup#include <Excel.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 615, 438, 192, 124) $Checkbox1 = GUICtrlCreateCheckbox("Checkbox1", 40, 16, 25, 17) $Checkbox2 = GUICtrlCreateCheckbox("Checkbox2", 80, 16, 33, 17) $Checkbox3 = GUICtrlCreateCheckbox("Checkbox3", 120, 16, 41, 17) $Checkbox4 = GUICtrlCreateCheckbox("Checkbox4", 168, 16, 25, 17) $Checkbox5 = GUICtrlCreateCheckbox("Checkbox5", 200, 16, 33, 17) $Checkbox6 = GUICtrlCreateCheckbox("Checkbox6", 232, 16, 25, 17) $Checkbox7 = GUICtrlCreateCheckbox("Checkbox7", 272, 16, 41, 17) $Checkbox8 = GUICtrlCreateCheckbox("Checkbox8", 320, 16, 97, 17) $Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17) $Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Case $button1 GUICtrlRead $checkbox1 if $checkbox1 = checked then $oExcel.Selection.Autofilter("1",'Y') elseif $checkbox1 = unchecked then $oExcel.Selection.Autofilter("1",'N') Exit EndSwitch WEnd
BrewManNH Posted December 11, 2013 Posted December 11, 2013 Here's how I'd do it. #include <Excel.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 615, 438, 192, 124) Global $Checkbox[8] For $I = 0 To 7 $Checkbox[$I] = GUICtrlCreateCheckbox("Checkbox" & $I + 1, 10 + (75 * $I), 16) Next $Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17) $Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 For $I = 0 To 7 If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : CHECKED = ' & "CHECKED" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ;~ $oExcel.Selection.Autofilter("1", 'Y') Else ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : CHECKED = ' & "UNCHECKED" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ;~ $oExcel.Selection.Autofilter("1", 'N') EndIf Next EndSwitch WEnd Currently this only prints to the output pane in Scite whether the checkbox is checked or not, easily adapted to doing what you want with it. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
13lack13lade Posted December 11, 2013 Author Posted December 11, 2013 thanks brewman, alot simpler than i imagined. greatly appreciated!
13lack13lade Posted December 11, 2013 Author Posted December 11, 2013 (edited) wasnt sure how to include your loop into looping through the excel columns matching criteria, got it filtering now though just has alot of code rather than looping! lol expandcollapse popup#include <Excel.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 680, 438, 192, 124) $Checkbox = GUICtrlCreateCheckbox("Checkbox", 1, 10, 16) $Checkbox2 = GUICtrlCreateCheckbox("Checkbox", 30, 10, 16) $Checkbox3 = GUICtrlCreateCheckbox("Checkbox", 60, 10, 16) $Checkbox4 = GUICtrlCreateCheckbox("Checkbox", 90, 10, 16) $Checkbox5 = GUICtrlCreateCheckbox("Checkbox", 120, 10, 16) $Checkbox6 = GUICtrlCreateCheckbox("Checkbox", 150, 10, 16) $Checkbox7 = GUICtrlCreateCheckbox("Checkbox", 180, 10, 16) $Checkbox8 = GUICtrlCreateCheckbox("Checkbox", 210, 10, 16) $Checkbox9 = GUICtrlCreateCheckbox("Checkbox", 240, 10, 16) $Checkbox10 = GUICtrlCreateCheckbox("Checkbox", 270, 10, 16) $Checkbox11 = GUICtrlCreateCheckbox("Checkbox", 300, 10, 16) $Checkbox12 = GUICtrlCreateCheckbox("Checkbox", 330, 10, 16) $Checkbox13 = GUICtrlCreateCheckbox("Checkbox", 360, 10, 16) $Checkbox14 = GUICtrlCreateCheckbox("Checkbox", 390, 10, 16) $Checkbox15 = GUICtrlCreateCheckbox("Checkbox", 420, 10, 16) $Checkbox16 = GUICtrlCreateCheckbox("Checkbox", 450, 10, 16) $Checkbox17 = GUICtrlCreateCheckbox("Checkbox", 480, 10, 16) $Checkbox18 = GUICtrlCreateCheckbox("Checkbox", 510, 10, 16) $Checkbox19 = GUICtrlCreateCheckbox("Checkbox", 540, 10, 16) $Checkbox20 = GUICtrlCreateCheckbox("Checkbox", 570, 10, 16) $Checkbox21 = GUICtrlCreateCheckbox("Checkbox", 600, 10, 16) $Checkbox22 = GUICtrlCreateCheckbox("Checkbox", 630, 10, 16) $Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17) $Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### $oExcel = _ExcelBookOpen("\\fbnecl3\inzb\Documents\Load Support\Tom\Projects\Current\Market Schedule Code List.xlsx",1) $oExcel.Sheets("ASIA" ).Select While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 ; For $I = 0 To 7 If GUICtrlRead($Checkbox) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(2, "Y") Else $oExcel.Selection.Autofilter(2, "N") EndIf If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(3, "Y") Else $oExcel.Selection.Autofilter(3, "N") EndIf If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(4, "Y") Else $oExcel.Selection.Autofilter(4, "N") EndIf If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(5, "Y") Else $oExcel.Selection.Autofilter(5, "N") EndIf If GUICtrlRead($Checkbox5) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(6, "Y") Else $oExcel.Selection.Autofilter(6, "N") EndIf If GUICtrlRead($Checkbox6) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(7, "Y") Else $oExcel.Selection.Autofilter(7, "N") EndIf If GUICtrlRead($Checkbox7) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(8, "Y") Else $oExcel.Selection.Autofilter(8, "N") EndIf If GUICtrlRead($Checkbox8) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(9, "Y") Else $oExcel.Selection.Autofilter(9, "N") EndIf If GUICtrlRead($Checkbox9) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(10, "Y") Else $oExcel.Selection.Autofilter(10, "N") EndIf If GUICtrlRead($Checkbox10) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(11, "Y") Else $oExcel.Selection.Autofilter(11, "N") EndIf If GUICtrlRead($Checkbox11) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(12, "Y") Else $oExcel.Selection.Autofilter(12, "N") EndIf If GUICtrlRead($Checkbox12) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(13, "Y") Else $oExcel.Selection.Autofilter(13, "N") EndIf If GUICtrlRead($Checkbox13) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(14, "Y") Else $oExcel.Selection.Autofilter(14, "N") EndIf If GUICtrlRead($Checkbox14) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(15, "Y") Else $oExcel.Selection.Autofilter(15, "N") EndIf If GUICtrlRead($Checkbox15) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(16, "Y") Else $oExcel.Selection.Autofilter(16, "N") EndIf If GUICtrlRead($Checkbox16) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(17, "Y") Else $oExcel.Selection.Autofilter(17, "N") EndIf If GUICtrlRead($Checkbox17) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(18, "Y") Else $oExcel.Selection.Autofilter(18, "N") EndIf If GUICtrlRead($Checkbox18) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(19, "Y") Else $oExcel.Selection.Autofilter(19, "N") EndIf If GUICtrlRead($Checkbox19) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(20, "Y") Else $oExcel.Selection.Autofilter(20, "N") EndIf If GUICtrlRead($Checkbox20) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(21, "Y") Else $oExcel.Selection.Autofilter(21, "N") EndIf If GUICtrlRead($Checkbox21) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(22, "Y") Else $oExcel.Selection.Autofilter(22, "N") EndIf If GUICtrlRead($Checkbox22) = $GUI_CHECKED Then $oExcel.Selection.Autofilter(23, "Y") Else $oExcel.Selection.Autofilter(23, "N") EndIf ; Next EndSwitch WEnd Now i need to determine what the specific code is (this comes from column A in the autofilter result) or in other words i need to return the last visable cell value in column A, then i can change the text of my label to the result. anyone able to provide me with code on _excelreadcell( last visable cell in column a? ) Edited December 12, 2013 by 13lack13lade
BrewManNH Posted December 12, 2013 Posted December 12, 2013 (edited) My script example will easily deal with removing 70% of the code you wrote in post 6. Instead of using the consolewrites in my example, replace it with this and get rid of that long redundant, and unnecessary, code. If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then $oExcel.Selection.Autofilter($I, "Y") Else $oExcel.Selection.Autofilter($I, "N") EndIf This uses the same looping method, and replaces a LOT of the comparison code. Edited December 12, 2013 by BrewManNH If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
13lack13lade Posted December 12, 2013 Author Posted December 12, 2013 Ahh so i did have it coded right when i did that, i dont know what else i did that made it not work, will change it back. Thanks brewman How can i find the last visable cell in column a?
BrewManNH Posted December 12, 2013 Posted December 12, 2013 I'd look at the Excel functions, or water's rewrite of it, I don't automate Excel so I have no idea. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
13lack13lade Posted December 18, 2013 Author Posted December 18, 2013 Okay i have done some more research and i think ive found all the bits of code i need however im having some trouble trying join them all together correctly to get what i want. Can anyone help me join these bits of code together to ultimate get what im trying to do? This code controls the autofilter - #include <Excel.au3> #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 615, 438, 192, 124) Global $Checkbox[8] For $I = 0 To 7 $Checkbox[$I] = GUICtrlCreateCheckbox("Checkbox" & $I + 1, 10 + (75 * $I), 16) Next $Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17) $Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### $oExcel = _ExcelBookOpen("\\fbnecl3\inzb\Documents\Load Support\Tom\Projects\Current\Market Schedule Code List.xlsx",1) $oExcel.Sheets("ASIA" ).Select $oExcel.Range("B4:X4").Select While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 For $I = 0 To 7 If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then $oExcel.Selection.Autofilter($I, "Y") Else $oExcel.Selection.Autofilter($I, "N") EndIf Next EndSwitch WEnd This is the code ive found to find the visible cell in column A or the filtered data i am pretty sure what im needing is in this bit of code but im not sure how to change it to get it to do what i want:( Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) ; Extract integer last row and col $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3) Local $iLastRow = $sLastCell[0] Local $iLastColumn = $sLastCell[1] ConsoleWrite($iLastRow & ' ' & $iLastColumn & @LF) for $1 = 2 to $iLastRow if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then Else ConsoleWrite($oExcel.Activesheet.Cells($1,1).Value & @LF) EndIf _ExcelBookClose($oExcel, 0, 0) So instead of the last line - ConsoleWrite($oExcel.Activesheet.Cells($1,1).Value & @LF) that would need to be GUISetControl ( $label1, $slastcell?, left, top, width, height)
13lack13lade Posted January 2, 2014 Author Posted January 2, 2014 still after some help for this if anyone can assist?
13lack13lade Posted January 21, 2014 Author Posted January 21, 2014 still need help if theres any autoit to excel gurus out there!
BrewManNH Posted January 22, 2014 Posted January 22, 2014 You should probably split the Excel part of your thread from the checkbox question you started with as the title doesn't match your problem now. Start a new thread with the relevant information and what you need instead of bumping a non-related thread (non-related in that it doesn't have anything to do about checkboxes now) If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
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