Jump to content
Khasck

Searching an Excel file

Recommended Posts

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

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

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)

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)

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

×
×
  • Create New...