Search for a value inside an excel doc

[t1ck3ts]

Hi Guys

Long time lurker and AutoIt Lover!

I love playing around with AutoIt and just make some little apps to make my life easy, along with my work tasks simple and easy to do

Now, i've come to a reall unknown field of how AutoIt does this and thats working with Excel documents.

I would like to search inside my excel document and find the word "Machine01" which will always be on Sheet: 2, Column: A

just the row might be differant, depending on the machine i want to look for

Excel example:

------[ A ]------[ B ]-----
1 | Machine01 |      |
2 | Machine02 |      |
3 | Machine03 |      |

So i want to find "Machine01" and then put the value inside Column: B, on the same row "Machine01" is on.

I am grabbing the name of what i want to search for from some software, this can change depending on where the person using the software, wants to start, ie) he clicks on "Machine23"

and my autoit app writes the value in the column next to it, then he may want to jump to "Machine01" so the app needs to put the data in the coloumn next to that, this is why i need it to look for where it is before putting the date in.

So, if i was on the right path, the code would go something like this. (This wont work i know )

_ExcelWriteCell($oExcel, "data goes here", ControlTreeView("DNA", "", "SysTreeView321", "GetSelected"), 2)

My code so far:

#include <Excel.au3>

Local $sFilePath = @ScriptDir & "\" & "MachineStatus.xls"
Local $oExcel = _ExcelBookOpen($sFilePath, 0)

_ExcelSheetActivate($oExcel, 2)






_ExcelBookSaveAs($oExcel, @ScriptDir & "\" & "MachineStatus_Update.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

Hahah, as you can see i left out a space where the code would go to search and write to cell would go.

Any help would be greatly appreciated

Edited February 12, 2013 by t1ck3ts

[water]

The has a _ExcelFindInRange function.

[t1ck3ts]

The has a _ExcelFindInRange function.

Thanks water I just saw your post & code in and altered it to give me what im looking for

Although i had to add a sleep function to allow it to activate sheet 2 and search in that, else it was always using sheet 1

#include <excelcom_udf.au3>
#include <array.au3>

$sFilePath = @ScriptDir & "\" & "MachineStatus.xls"
$oExcel = _ExcelBookOpen($sFilePath, 0)
$ConcatVal = "HamTills"

_ExcelSheetActivate($oExcel, 2)

Sleep(500)

$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200")

_ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 1)

_ExcelBookSaveAs($oExcel, @ScriptDir & "\" & "MachineStatus_1.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

Edited February 12, 2013 by t1ck3ts

[water]

Glad your problem could be solved

[t1ck3ts]

Glad your problem could be solved

Argh I wish, maybe you could look at this code and see how or why it would not be working.

While 1 = 1

$KXRName = ControlTreeView("DNA", "", "SysTreeView321", "GetSelected") ; Take the full name of the machine ie. "System01|System (443)"
$FirstPosition = StringInStr($KXRName, "|", 0, 1) ; Count how many characters to the first "|" in the name
$SetTextLeft = StringMid($KXRName, $FirstPosition + 1) ; Remove everything and including the first "|"
$SecondPosition = StringInStr($SetTextLeft, " ", 0, 1) ; Take the new name and find the first " " (space)
$FinalText = StringLeft($SetTextLeft, $SecondPosition - 1) ; Remove everything after and including the space

If ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") <= 0 Then ; If the correct window is not opened
Sleep(500) ; wait
If ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") > 0 Then ; If the correct window is opened

$days = ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount") ; set $days to the ammount calculated inside the window
$aFound = _ExcelFindInRange($oExcel, $FinalText, "A1:A56") ; Find the name inside the excel document

TrayTip("DNA Day Calculator", $FinalText & ": " & $days & " days found.", 1, 1) ; Send a system tray tooltip with how many days found
_ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name
ControlClick("Open File", "", "[CLASSNN:Button2]", "") ; Send a click to the close button on the window
ContinueLoop ; Continue looking for the next window to open up
EndIf
EndIf
WEnd

Basically, this is the heart of my little app. I dont know if i did it correctly, but it was working fine before i wanted to do the whole search function inside an excel document.

It is working, but after i click on another machine to get its data, it kills the app.

I needed this app just to sit in the system tray and just wait for the right window to be opend and then grab the data and send it to the excel document, obviously, depending on

what machine he clicked on, is the data it must write for.

Have i done this the wrong way or am i missing something?

Edited February 12, 2013 by t1ck3ts

[water]

You need to do some error checking after the _Excel* functions. Use "MsgBox" or "ConsoleWrite" to output the value of @error.

[t1ck3ts]

Think i found out why

There was no cell with the name of the machine i was trying to look for >_<

[water]

But still: adding some error checks to your script is a good idea!

[t1ck3ts]

But still: adding some error checks to your script is a good idea!

Hahahahah, im still new to AutoIt I wouldnt know where to add them, but im learning new stuff every minute!!

[water]

Something like this:

_ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name
If @error then MsgBox(0, "Error", "_ExcelWriteCell returned error " & @error)

Edited February 12, 2013 by water

[t1ck3ts]

Something like this:

_ExcelWriteCell($oExcel, $days, $aFound[1][3], $aFound[1][2] + 1) ; write the days down in the next column on the same row as the machine name
If @error then MsgBox(0, "Error", "_ExcelWriteCell returned error " & @error)

Ok, then with the @error, it will return the error number, which can be found inside: excelcom_udf.au3

Im guessing?

edit: Yeah, found them

; Return Value(s): On Success - Returns 1
;                On Failure - Returns 0 and sets @error on errors:
;                @error=1 - Specified object does not exist
;                @error=2 - Starting row or column invalid
;                    @extended=0 - Starting row invalid
;                    @extended=1 - Starting column invalid
;                @error=3 - Ending row or column invalid
;                    @extended=0 - Ending row invalid
;                    @extended=1 - Ending column invalid

Edited February 12, 2013 by t1ck3ts

[water]

Exactly. This helps debugging your code!