zxtnt09

[Solved] Excel - Search Tool

18 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by JLogan3o13
1 person likes this

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

Share this post


Link to post
Share on other sites

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 ).

:ermm:

Share this post


Link to post
Share on other sites

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?

1 person likes this

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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:sweating:,

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

Edited by zxtnt09

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by nitekram
Fixed syntex on code

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

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 ,

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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... :ermm:

1 ) forget "Excel" :D

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

Edited by zxtnt09

Share this post


Link to post
Share on other sites

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.

1 person likes this

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

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 ?!

Share this post


Link to post
Share on other sites

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

 

1 person likes this

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

; 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

Share this post


Link to post
Share on other sites

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

 

1 person likes this

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

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" ?

Share this post


Link to post
Share on other sites

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.

1 person likes this

√-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