Sign in to follow this  
Followers 0
Bearpocalypse

Reading an Excel sheet and returning adjacent cells

4 posts in this topic

I'm fairly new to AutoIt and I'm getting in the groove of it all, but I'm needing a little bit of help. Before I post the code I have for you all to ridicule me on, let me tell you what I'm wanting to do. I have an input box that I enter a PC name in. When I hit my search button, I have a function that returns the print group(s) that the PC is in (in what I have named as $List1 right now). What I am wanting to do is be able to click the print group via MouseGetPos and have that run another function to search through an Excel file that is stored via the intranet. The print groups will be in column A in the sheet and I want to return the printer in column B and C and put it in a second list box (what is named $List2).

I've run into a rut trying to get this working, I've checked out the help files and rummaged through the forums. And here is my code, please be gentle. Any help is appreciated and guidance is welcome!

Local $oExcel = _ExcelBookOpen("http://intranet sheet.xls")


Local $aArray = _ExcelReadSheetToArray($oExcel)
   $sSearch = guictrlread($List1) MouseGetPos(0) & MouseGetPos(1)
   $row = UBound($aArray)
   $column = UBound($aArray, 2)
   $iIndex = _ArraySearch($aArray, $sSearch, "", "", "", "", $Column, $row)
   
 Local $pGroup = _ExcelReadCell($oExcel, $row, 1) ;pGroup is the print group.
 Local $mPrinter = _ExcelReadCell($oExcel, $row, 2) ;mPrinter is the main printer.
 local $sPrinter = _ExcelReadCell($oExcel, $row, 3) ;sPrinter is the secondary printer.
 
 GUICtrlSetData($List2, $mPrinter, $sPrinter)

Share this post


Link to post
Share on other sites



I would avoid reading the entire sheet to an array if possible, unless you're using the data elsewhere. It might be faster just to find the number of rows, and then cycle through Column A until you have a match. Below is a short reproducer, based on the screenshot, to give you an idea:

post-54985-0-63844300-1374700369_thumb.p

#include <Excel.au3>

$group = "Group 10"

$oExcel = _ExcelBookOpen(@DesktopDir & "\Forum.xls")
    $oSheet = $oExcel.Worksheets(1) 
    $oSheet.Activate                                        ;Activate the appropriate sheet
    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate
    $newRow = $oExcel.ActiveCell.Row + 1                    ;Obtain the next open row

For $i = 2 To $newRow
    $cell = _ExcelReadCell($oExcel, $i, 1)
        If $cell = $group Then
            $mPrinter = _ExcelReadCell($oExcel, $i, 2)
            $sPrinter = _ExcelReadCell($oExcel, $i, 3)
        EndIf
Next

MsgBox(0, "", "Main Printer is: " & $mPrinter & @CRLF & @CRLF & "Secondary Printer is: " & $sPrinter)

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

You sir are a gentleman and a scholar. Works like a champ once I got unstupid and put in the correct path. Got a couple of features I want to get added in, but I got a good setup now thanks to you. Thanks again!

Share this post


Link to post
Share on other sites

Glad to be of help :)


√-1 2^3 ∑ π, and it was delicious!

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