Sign in to follow this  
Followers 0
t1ck3ts

Search for a value inside an excel doc

12 posts in this topic

#1 ·  Posted (edited)

Hi Guys

Long time lurker and AutoIt Lover! :sweating:

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 o:)

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 :D)

_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 :wub:

Edited by t1ck3ts

Share this post


Link to post
Share on other sites



The has a _ExcelFindInRange function.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by t1ck3ts

Share this post


Link to post
Share on other sites

Glad your problem could be solved :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Glad your problem could be solved :D

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 by t1ck3ts

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Think i found out why :sweating:

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 :shifty:

; 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 by t1ck3ts

Share this post


Link to post
Share on other sites

Exactly. This helps debugging your code!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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