[Solved] Excel - Search Tool

[zxtnt09]

hi guys,

did anyone know that , how can i use a search for .xlsx (Excel) files?.

i have a exel file and it's have 2 Column ,

Column's Like :

1 abc

2 bca

3 cda

4 oxh

...

it's meant 1 for "abc" , 2 for "bca" , ...

now i want to create a search tool for this , for example : if you search "abc" , you see "1".

if this script doing by "if then" is better i think!

for example (something like that , i know it's not true): 

if abc then
echo 1
else 
echo "error string is wrong"

thanks guys , and sorry for my bad english 

Edited April 30, 2015 by zxtnt09

[JLogan3o13]

Take a look at the excel functions in the help file. You could do something like this to return all instances of 'abc':

#include <Array.au3>
#include <Excel.au3>

Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

Local $aResult = _Excel_RangeFind($oWorkbook, "abc")
_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)

 

Edited April 28, 2015 by JLogan3o13

[zxtnt09]

Take a look at the excel functions in the help file. If you're searching for which column is "abc", you could do something like this:

#include <Array.au3>
#include <Excel.au3>

Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

Local $aResult = _Excel_RangeFind($oWorkbook, "abc")
_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)

 

​Thanks for your replay,

but! , it's run with "Excel"!

can i use all of my excel in au3 ?

it's meant , don't Load "Test.xlsx" Files!

and it's just searching inside ( in program codes ).

[JLogan3o13]

I think there is a language barrier. Correct me if I am wrong, but you're asking if you can search inside the file without actually opening it?

[zxtnt09]

I think there is a language barrier. Correct me if I am wrong, but you're asking if you can search inside the file without actually opening it?

​ohh,

i think step by step can helping hah ,

1 ) i have a excl file 

2 ) copy/paste all of excl files duc in au3 file, for ex : 

$search = GUICtrlCreateButton("Search",272, 83, 75 , 25 )
GUICtrlSetColor(-1, 0x6699FF)
GUISetState(@SW_SHOW)
GUICtrlSetBkColor($search, 0xffffff)

;My excl file 
1 abc
2 bac
3 oasm
4 oamdasn
5 andfnnASd
;end my excl file


;search tool begin (i don't know how can creat )

;for example : search 1 then result show abc

in other way , we have 2 variable 

1 ) int         2 ) string

thanks 

Edited April 28, 2015 by zxtnt09

[JLogan3o13]

I'm still not getting what you want. You are reading from an Excel file, and you're inputting into a GUI (something you might have mentioned to begin with). So are you looking to put it into a ListView, like so?

If this is not what you're after then I'm out until you can find a way to explain more clearly what you want.

#include <Array.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>


Local $oAppl = _Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2) ;Read all used cells in column A
_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)


GUICreate("Test", 300, 300)
$hListView = GUICtrlCreateListView("Row | Excel data", 10, 10, 120, 280)

For $i = 0 To UBound($aResult) - 1
    GUICtrlCreateListViewItem($i + 1 & "|" & $aResult[$i], $hListView)
Next


GUISetState(@SW_SHOW)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
        EndSwitch
    WEnd

GUIDelete()

Edited April 28, 2015 by JLogan3o13

[zxtnt09]

I'm still not getting what you want. You are reading from an Excel file, and you're inputting into a GUI (something you might have mentioned to begin with). So are you looking to put it into a ListView, like so?

If this is not what you're after then I'm out until you can find a way to explain more clearly what you want.

#include <Array.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>


Local $oAppl = _Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2) ;Read all used cells in column A
_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)


GUICreate("Test", 300, 300)
$hListView = GUICtrlCreateListView("Row | Excel data", 10, 10, 120, 280)

For $i = 0 To UBound($aResult) - 1
    GUICtrlCreateListViewItem($i + 1 & "|" & $aResult[$i], $hListView)
Next


GUISetState(@SW_SHOW)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
        EndSwitch
    WEnd

GUIDelete()

​i'm so sorry for my bad english...

it's my xlsx file : 

now, i copy/paste all of "A" & "B" Colums in my script ( i don't want it's read from some where like : @Script Dir , ... )

after i copy the colums , 

in my program create something like that : 

after i search : "215215" ( in Text box ) and press "Ok" , i see "zxctnt09" in Result.

thanks and sorry for kill your time 

 

[nitekram]

When you click the OK button, you will have to read the Search box...it would be easier if you put your code out, but anyway you will need to add this to your OK button

 

; all this would go in your OK box, after the button is pressed.
; not tested 

Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

; here you need to read you search box

$sSearchBox = GuiCtrlRead($hSearchBox)

Local $aResult = _Excel_RangeFind($oWorkbook, $sSearchBox)

; if there are more than one instance you may need to have another button called Next and loop through the different values found

GuiCtrlSetData($hResults, $aResult[1])
;_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)

 

.

 

 

Edited April 29, 2015 by nitekram
Fixed syntex on code

[zxtnt09]

When you click the OK button, you will have to read the Search box...it would be easier if you put your code out, but anyway you will need to add this to your OK button

 

; all this would go in your OK box, after the button is pressed.
; not tested 

Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")

; here you need to read you search box

$sSearchBox = GuiCtrlRead($hSearchBox)

Local $aResult = _Excel_RangeFind($oWorkbook, $sSearchBox)

; if there are more than on instance you may need to have another button called Next

GuiCtrlSetData($hResults, $aResult[1])
;_ArrayDisplay($aResult)

_Excel_BookClose($oWorkbook)
_Excel_Close($oAppl)

 

.

 

 

 

but i don't like read file from any directory.

and if read , should from web server.

and second , the code have problem.

​Thanks dear ,

[nitekram]

What you are reading is the search string (from the image you posted, it would be coming from the TEXT BOX) that is going to be inputted from the end user...that is what you are trying to do, right? Have the end user search through an excel file for a string and then pull the data from the adjacent cell?

 

The part that it reads the file from, can be any location...you just have to put the path to the excel file that you will need to load before you can search it. Also, in my comments (I wrote not tested) -->> the code does not work, as I have no clue what your variables are called as you have not posted any code. Please post an example script with your variable names, and we may be able to help, but as has been asked, we need input from you, in order to figure out what you are trying to do.

Edited April 29, 2015 by nitekram

[zxtnt09]

What you are reading is the search string (from the image you posted, it would be coming from the TEXT BOX) that is going to be inputted from the end user...that is what you are trying to do, right? Have the end user search through an excel file for a string and then pull the data from the adjacent cell?

 

The part that it reads the file from, can be any location...you just have to put the path to the excel file that you will need to load before you can search it. Also, in my comments (I wrote not tested) -->> the code does not work, as I have no clue what your variables are called as you have not posted any code. Please post an example script with your variable names, and we may be able to help, but as has been asked, we need input from you, in order to figure out what you are trying to do.

​i think , i should say that by other way... 

1 ) forget "Excel"

2 ) i have some stings , and integer.

3 ) each string have a integer "ID" , for ex : integer : 10000 have this string : "abc".

4 ) now!, i want to create search box for this integer and string. i think it can create by 3 way : 

A ) By Array

B ) By "if then"

C ) the way you know is Better

5 ) i have two file ( .txt ) ,

A ) integers

B ) Strings

• Each Line Of string and integer are 2 equal

Example (out of this script) : 

"We Have 3 Name , And 3 Number",

81 : John

93 : Willam

31 : Sarah

in program : when you search "81" , you see "john" else ( search other this 3 numbers , you see error ) 

did can create something like that ?! 

and sorry for my harassment 

Edited April 29, 2015 by zxtnt09

[nitekram]

You can do that in AutoIt - yes. Please show your attempt and then we will look at trying to fix any issues you might be having.

[zxtnt09]

You can do that in AutoIt - yes. Please show your attempt and then we will look at trying to fix any issues you might be having.

​hi again,

; using a 2D array

#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $avArray[6][2] = [ _
        ["String0", "SubString0"], _
        ["String1", "SubString1"], _
        ["String2", "SubString2"], _
        ["String3", "SubString3"], _
        ["String4", "SubString4"], _
        ["String5", "SubString5"]]

_ArrayDisplay($avArray, "$avArray")

Local $sSearch = InputBox("_ArraySearch() demo", "String to find?")
If @error Then Exit

Local $sColumn = InputBox("_ArraySearch() demo", "Column to search?")
If @error Then Exit
$sColumn = Int($sColumn)

Local $iIndex = _ArraySearch($avArray, $sSearch, 0, 0, 0, 1, 1, $sColumn)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Not Found", '"' & $sSearch & '" was not found on column ' & $sColumn & '.')
Else
    MsgBox($MB_SYSTEMMODAL, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.')
EndIf

like that,

just one thing, remove that : 

after you close that , you see : 

can you help me ?!

[nitekram]

Maybe something like this?

 

; using a 2D array

#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $avArray[6][2] = [ _
        ["String0", "SubString0"], _
        ["String1", "SubString1"], _
        ["String2", "SubString2"], _
        ["String3", "SubString3"], _
        ["String4", "SubString4"], _
        ["String5", "SubString5"]]

_ArrayDisplay($avArray, "$avArray")

Local $sSearch = InputBox("_ArraySearch() demo", "String to find?")
If @error Then Exit
Local $FoundValue = _ArraySearch($avArray, $sSearch)
MsgBox('','', $avArray[$FoundValue][1])
Exit

 

[zxtnt09]

; using a 2D array

#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $avArray[6][2] = [ _
        ["String0", "SubString0"], _
        ["String1", "SubString1"], _
        ["String2", "SubString2"], _
        ["String3", "SubString3"], _
        ["String4", "SubString4"], _
        ["String5", "SubString5"]]

;Deleted _ArrayDisplay($avArray, "$avArray")

Local $sSearch = InputBox("_ArraySearch() demo", "String to find?")
If @error Then Exit

Local $sColumn = InputBox("_ArraySearch() demo", "Column to search?")
If @error Then Exit
$sColumn = Int($sColumn)

Local $iIndex = _ArraySearch($avArray, $sSearch, 0, 0, 0, 1, 1, $sColumn)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Not Found", '"' & $sSearch & '" was not found on column ' & $sColumn & '.')
Else
    MsgBox($MB_SYSTEMMODAL, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn & '.')
EndIf

Problem 1 : fixed => it was remove : 

Problem 2 : i don't know how can i use just "one" search box

[232showtime]

something like this???

; using a 2D array

#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $avArray[6][2] = [ _
        ["String0", "SubString0"], _
        ["String1", "SubString1"], _
        ["String2", "SubString2"], _
        ["String3", "SubString3"], _
        ["String4", "SubString4"], _
        ["String5", "SubString5"]]

;Deleted _ArrayDisplay($avArray, "$avArray")

Local $sSearch = InputBox("_ArraySearch() demo", "String to find?")
If @error Then Exit


Local $iIndex = _ArraySearch($avArray, $sSearch, 0, 0, 0, 1, 1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Not Found", '"' & $sSearch & '" was not found on column ' & '.')
Else
    MsgBox($MB_SYSTEMMODAL, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & '.')
EndIf

 

[zxtnt09]

something like this???

; using a 2D array

#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $avArray[6][2] = [ _
        ["String0", "SubString0"], _
        ["String1", "SubString1"], _
        ["String2", "SubString2"], _
        ["String3", "SubString3"], _
        ["String4", "SubString4"], _
        ["String5", "SubString5"]]

;Deleted _ArrayDisplay($avArray, "$avArray")

Local $sSearch = InputBox("_ArraySearch() demo", "String to find?")
If @error Then Exit


Local $iIndex = _ArraySearch($avArray, $sSearch, 0, 0, 0, 1, 1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Not Found", '"' & $sSearch & '" was not found on column ' & '.')
Else
    MsgBox($MB_SYSTEMMODAL, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & '.')
EndIf

 

​Thanks it's Done! 

and how can i submit for example : 

"abc" for search on "123842" ?

[JLogan3o13]

Could you please stop quoting everyone every time you reply? We know what we said, and it is making the thread needlessly long to scroll through.