Sign in to follow this  
Followers 0
gte

How to search for a value in excel, then reference a column to the right of it and grab the value?

2 posts in this topic

I need to reference a network share excel spreadsheet, have my script search for a value and then go 2 columns to the right of the value it searched for and grab that value and populate a variable with it?

Anythoughts on how do do that?

I started doing the following, but I'm not sure if I'm headed down the correct path, and I'm not sure how to get it to grab the data out of the array?

#include <Excel.au3>

$sitepoccontactlist = '\\servername\share\folder\folder\folder\file.xls'


$sitepocarray = _ExcelReadSheetToArray($sitepoccontactlist)


;~ _ExcelReadArray($sitepoccontactlist, 1, 2, 0)

$sitepocextracted = _ArraySearch($sitepocarray, $fax_server_name,) 
if @error Then
    MsgBox(0, 'Error', 'Site POC list cannot be loaded for automatic emails')
EndIf

Share this post


Link to post
Share on other sites



I need to reference a network share excel spreadsheet, have my script search for a value and then go 2 columns to the right of the value it searched for and grab that value and populate a variable with it?

Anythoughts on how do do that?

I started doing the following, but I'm not sure if I'm headed down the correct path, and I'm not sure how to get it to grab the data out of the array?

#include <Excel.au3>

$sitepoccontactlist = '\\servername\share\folder\folder\folder\file.xls'


$sitepocarray = _ExcelReadSheetToArray($sitepoccontactlist)


;~ _ExcelReadArray($sitepoccontactlist, 1, 2, 0)

$sitepocextracted = _ArraySearch($sitepocarray, $fax_server_name,) 
if @error Then
    MsgBox(0, 'Error', 'Site POC list cannot be loaded for automatic emails')
EndIf
Assuming _ExcelReadSheetToArray() is working for you, the problem is _ArraySearch() which only searches one column at a time on 2D arrays:
#include <Array.au3>

; Create simulated result of _ExcelReadSheetToArray()
Global $avArray[6][6]
$avArray[0][0] = UBound($avArray) - 1; Row count
$avArray[0][1] = UBound($avArray, 2) - 1; Col count
For $r = 1 To $avArray[0][0]
    For $c = 1 To UBound($avArray, 2) - 1
        $avArray[$r][$c] = "R" & $r & "C" & $c; Sim data
    Next
Next
_ArrayDisplay($avArray, "Debug: $avArray")

; Find data in array
For $c = 1 To $avArray[0][1]
    $iFound = _ArraySearch($avArray, "R2C2", 1, 0, 0, 0, 1, $c); Search one column at a time
    If @error Then
        ConsoleWrite("Debug: _ArraySearch() column " & $c & " failed, @error = " & @error & @LF)
    Else
        ConsoleWrite("Debug: Found; row = " & $iFound & ", column = " & $c & @LF)
        ConsoleWrite("Debug: Target data Is at row = " & $iFound & ", column = " & $c + 2 & _
                ", text = " & $avArray[$iFound][$c + 2] & @LF)
        ExitLoop
    EndIf
Next

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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
Sign in to follow this  
Followers 0