Khasck

Searching an Excel file

9 posts in this topic

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)

 

post-82944-0-79169000-1426807259.jpg

Share this post


Link to post
Share on other sites



#3 ·  Posted (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 by Khasck

Share this post


Link to post
Share on other sites

#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.

Share this post


Link to post
Share on other sites
#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? 

Share this post


Link to post
Share on other sites

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)

1 person likes this

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

Thank you very much, this helps a ton. 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now