t1ck3ts Posted February 12, 2013 Share Posted February 12, 2013 (edited) Hi GuysLong time lurker and AutoIt Lover! I love playing around with AutoIt and just make some little apps to make my life easy, along with my work tasks simple and easy to do Now, i've come to a reall unknown field of how AutoIt does this and thats working with Excel documents.I would like to search inside my excel document and find the word "Machine01" which will always be on Sheet: 2, Column: Ajust the row might be differant, depending on the machine i want to look forExcel example:------[ A ]------[ B ]----- 1 | Machine01 | | 2 | Machine02 | | 3 | Machine03 | |So i want to find "Machine01" and then put the value inside Column: B, on the same row "Machine01" is on.I am grabbing the name of what i want to search for from some software, this can change depending on where the person using the software, wants to start, ie) he clicks on "Machine23"and my autoit app writes the value in the column next to it, then he may want to jump to "Machine01" so the app needs to put the data in the coloumn next to that, this is why i need it to look for where it is before putting the date in.So, if i was on the right path, the code would go something like this. (This wont work i know )_ExcelWriteCell($oExcel, "data goes here", ControlTreeView("DNA", "", "SysTreeView321", "GetSelected"), 2)My code so far:#include <Excel.au3> Local $sFilePath = @ScriptDir & "\" & "MachineStatus.xls" Local $oExcel = _ExcelBookOpen($sFilePath, 0) _ExcelSheetActivate($oExcel, 2) _ExcelBookSaveAs($oExcel, @ScriptDir & "\" & "MachineStatus_Update.xls", "xls", 0, 1) _ExcelBookClose($oExcel)Hahah, as you can see i left out a space where the code would go to search and write to cell would go.Any help would be greatly appreciated Edited February 12, 2013 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 The has a _ExcelFindInRange function. 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...
t1ck3ts Posted February 12, 2013 Author Share Posted February 12, 2013 (edited) The has a _ExcelFindInRange function. Thanks water I just saw your post & code in and altered it to give me what im looking for Although i had to add a sleep function to allow it to activate sheet 2 and search in that, else it was always using sheet 1 #include <excelcom_udf.au3> #include <array.au3> $sFilePath = @ScriptDir & "\" & "MachineStatus.xls" $oExcel = _ExcelBookOpen($sFilePath, 0) $ConcatVal = "HamTills" _ExcelSheetActivate($oExcel, 2) Sleep(500) $aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200") _ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 1) _ExcelBookSaveAs($oExcel, @ScriptDir & "\" & "MachineStatus_1.xls", "xls", 0, 1) _ExcelBookClose($oExcel) Edited February 12, 2013 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 Glad your problem could be solved 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...
t1ck3ts Posted February 12, 2013 Author Share Posted February 12, 2013 (edited) Glad your problem could be solved Argh I wish, maybe you could look at this code and see how or why it would not be working. While 1 = 1 $KXRName = ControlTreeView("DNA", "", "SysTreeView321", "GetSelected") ; Take the full name of the machine ie. "System01|System (443)" $FirstPosition = StringInStr($KXRName, "|", 0, 1) ; Count how many characters to the first "|" in the name $SetTextLeft = StringMid($KXRName, $FirstPosition + 1) ; Remove everything and including the first "|" $SecondPosition = StringInStr($SetTextLeft, " ", 0, 1) ; Take the new name and find the first " " (space) $FinalText = StringLeft($SetTextLeft, $SecondPosition - 1) ; Remove everything after and including the space If ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") <= 0 Then ; If the correct window is not opened Sleep(500) ; wait If ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") > 0 Then ; If the correct window is opened $days = ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") ; set $days to the ammount calculated inside the window $aFound = _ExcelFindInRange($oExcel, $FinalText, "A1:A56") ; Find the name inside the excel document TrayTip("DNA Day Calculator", $FinalText & ": " & $days & " days found.", 1, 1) ; Send a system tray tooltip with how many days found _ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name ControlClick("Open File", "", "[CLASSNN:Button2]", "") ; Send a click to the close button on the window ContinueLoop ; Continue looking for the next window to open up EndIf EndIf WEnd Basically, this is the heart of my little app. I dont know if i did it correctly, but it was working fine before i wanted to do the whole search function inside an excel document. It is working, but after i click on another machine to get its data, it kills the app. I needed this app just to sit in the system tray and just wait for the right window to be opend and then grab the data and send it to the excel document, obviously, depending on what machine he clicked on, is the data it must write for. Have i done this the wrong way or am i missing something? Edited February 12, 2013 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 You need to do some error checking after the _Excel* functions. Use "MsgBox" or "ConsoleWrite" to output the value of @error. 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...
t1ck3ts Posted February 12, 2013 Author Share Posted February 12, 2013 Think i found out why There was no cell with the name of the machine i was trying to look for >_< Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 But still: adding some error checks to your script is a good idea! 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...
t1ck3ts Posted February 12, 2013 Author Share Posted February 12, 2013 But still: adding some error checks to your script is a good idea!Hahahahah, im still new to AutoIt I wouldnt know where to add them, but im learning new stuff every minute!! Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 (edited) Something like this: _ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name If @error then MsgBox(0, "Error", "_ExcelWriteCell returned error " & @error) Edited February 12, 2013 by water 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...
t1ck3ts Posted February 12, 2013 Author Share Posted February 12, 2013 (edited) Something like this: _ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name If @error then MsgBox(0, "Error", "_ExcelWriteCell returned error " & @error) Ok, then with the @error, it will return the error number, which can be found inside: excelcom_udf.au3 Im guessing? edit: Yeah, found them ; Return Value(s): On Success - Returns 1 ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Starting row or column invalid ; @extended=0 - Starting row invalid ; @extended=1 - Starting column invalid ; @error=3 - Ending row or column invalid ; @extended=0 - Ending row invalid ; @extended=1 - Ending column invalid Edited February 12, 2013 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted February 12, 2013 Share Posted February 12, 2013 Exactly. This helps debugging your code! 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...
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