Khasck Posted March 19, 2015 Posted March 19, 2015 Hello all, I was really hoping I would figure this out on my own but so far no good. Basicaly, what I'm trying to achieve: I need to be able to search for a username in autoit and it return the computer name for that user. I have a list of usernames and computer names in two different columns in an Excel (.xlsx) file. I'm really struggling to grasp how the different Excel UDF stuff works. When I run the script shown below, I'm only getting a result of "Sheet1||$A$1993|username|username|" I need it to return something like "username|computer-name" from column A and column B. From using example scripts, this is what I have so far: #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oAppl = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf ; ***************************************************************************** ; Read the formulas of a cell range (all used cells in column A and B) ; ***************************************************************************** Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 2) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A and B") ; ***************************************************************************** ; Find all occurrences of value "username" (partial match) ; ***************************************************************************** Local $aResult2 = _Excel_RangeFind($oWorkBook, "username") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'username' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($aResult2, "Excel UDF: _Excel_RangeFind Example 1") I'm open to any recommendations. I feel like there is a simple solution to this, I just can't figure it out. I can use a different way to store the username/computer name information if there is a better way. I have tried searching for a solution on the forums and on google and haven't found what i'm looking for. (I've attached what my excel spreadsheet looks like format wise)
JohnOne Posted March 19, 2015 Posted March 19, 2015 What do you have in $aResult when you display it? Is the usernam and computer name there in the array? AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans.
Khasck Posted March 19, 2015 Author Posted March 19, 2015 (edited) What do you have in $aResult when you display it? Is the usernam and computer name there in the array? Yes, the first popup ($aResult) shows the entire excel spreadsheet (both column A and B ). I can see all the usernames and respective computer-names next to eachother. Edited March 19, 2015 by Khasck
Solution JohnOne Posted March 19, 2015 Solution Posted March 19, 2015 #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oAppl = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf ; ***************************************************************************** ; Read the formulas of a cell range (all used cells in column A and B) ; ***************************************************************************** Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 2) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.") ;_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A and B") Local $Username = "Fred" For $i = 0 To UBound($aResult) -1 ; loop though array If $aResult[$i][0] = $Username Then MsgBox(0, "Answer", $Username & " uses computer " & $aResult[$i][1]) ExitLoop EndIf Next AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans.
Khasck Posted March 20, 2015 Author Posted March 20, 2015 #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oAppl = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf ; ***************************************************************************** ; Read the formulas of a cell range (all used cells in column A and B) ; ***************************************************************************** Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:B"), 2) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.") ;_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A and B") Local $Username = "Fred" For $i = 0 To UBound($aResult) -1 ; loop though array If $aResult[$i][0] = $Username Then MsgBox(0, "Answer", $Username & " uses computer " & $aResult[$i][1]) ExitLoop EndIf Next Ah, that's perfect. Thank you so much! One other thing, I'm just noticing some of our users have logged into multiple machines - is there a way to show all matching computers?
JohnOne Posted March 20, 2015 Posted March 20, 2015 Local $Username = "Fred" $strComputers = "" For $i = 0 To UBound($aResult) -1 ; loop though array If $aResult[$i][0] = $Username Then $strComputers &= $aResult[$i][1] & " " EndIf Next MsgBox(0, "Answer", $Username & " uses computer " & $strComputers) Khasck 1 AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans.
Khasck Posted March 20, 2015 Author Posted March 20, 2015 Local $Username = "Fred" $strComputers = "" For $i = 0 To UBound($aResult) -1 ; loop though array If $aResult[$i][0] = $Username Then $strComputers &= $aResult[$i][1] & " " EndIf Next MsgBox(0, "Answer", $Username & " uses computer " & $strComputers) I hate to bug you again, but i'm way over my head at this point. How can i give each computer result its own variable so that I can plug it in later? Basically if i have 3 machines under "fred", I want them to save as variables "computer-name1", "computer-name2", "computer-name3" so i can plug them in elsewhere.
JohnOne Posted March 20, 2015 Posted March 20, 2015 Local $Username = "Fred" $strComputers = "" For $i = 0 To UBound($aResult) -1 ; loop though array If $aResult[$i][0] = $Username Then $strComputers &= $aResult[$i][1] & " " EndIf Next $strComputers = StringTrimRight($strComputers, 1) $astrComputers = StringSplit($strComputers, " ", 2) _ArrayDisplay($astrComputers) AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans.
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