Sign in to follow this  
Followers 0
iks2

Soft that search phone number in excel

4 posts in this topic

Hi everybody!

I would like to make a soft that searches phone numbers in an excel sheet.

In my excel sheet, I have a lot of names arranged in a column and in the column after we have phone numbers.

I would like that when I type the beginning of a name, if I click on a button, the script give me all name related with their phone numbers.

All seems to be very easy, yes, but the problem is that I don't want open the excel sheet physically. I mean I don't want that user that used the soft see the excel sheet opening.

Do you have an idea how to do that kind of application?

Thanks in advance for your help...

Share this post


Link to post
Share on other sites



Take a look at the following

CODE
#include <GUIConstants.au3>

#Region ### START Koda GUI section ###

$Form2 = GUICreate("Get SN#", 195, 95, 304, 220)

$txtID = GUICtrlCreateInput("", 16, 8, 70, 24)

GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")

$lblModel = GUICtrlCreateLabel("", 16, 64, 80, 17)

$lblSN = GUICtrlCreateLabel("", 104, 64, 80, 17)

$btnGetSN = GUICtrlCreateButton("GetSN", 112, 8, 65, 25)

$Label1 = GUICtrlCreateLabel("Model#", 16, 48, 40, 17)

$Label2 = GUICtrlCreateLabel("Serial#", 104, 48, 37, 17)

GUISetState(@SW_SHOW)

#EndRegion ### END Koda GUI section ###

While 1

$nMsg = GUIGetMsg()

Switch $nMsg

Case $GUI_EVENT_CLOSE

Exit

Case $txtID

GUICtrlSetData($lblSN,"")

GUICtrlSetData($lblModel,"")

EndSwitch

If $nMsg = $btnGetSN Then

$IDNumber = GUICtrlRead($txtID)

GetID($IDNumber)

EndIf

WEnd

Func GetID($ID)

$FileName="C:\Documents and Settings\Technician\Desktop\New Equipment Move\Query Test.xls"

if not FileExists($FileName) then

Msgbox (0,"Excel Test","Can't find database")

Exit

endif

$oExcel = ObjGet($FileName) ; Get an Excel Object from an existing filename

if IsObj($oExcel) then

; Tip: Uncomment these lines to make Excel visible

; $oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible

; $oExcelDoc.Application.Visible = 1; Set the application visible (without this Excel will exit)

; Some document properties do not return a value, we will ignore those.

$OEvent=ObjEvent("AutoIt.Error","nothing"); Equal to VBscript's On Error Resume Next

For $cell in $oExcel.ActiveSheet.Range("A1:A10000")

If $cell.Value = $ID Then

$ID = $cell.Value

$Row = $cell.Row

$SN = $oExcel.Activesheet.Cells($Row,"C").Value

$Model = $oExcel.Activesheet.Cells($Row,"B").Value

GUICtrlSetData($lblModel, $Model)

GUICtrlSetData($lblSN, $SN)

Endif

Next

else

Msgbox (0,"Excel Test","Error: Could not open database")

endif

EndFunc


[topic="21048"]New to AutoIt? Check out AutoIt 1-2-3![/topic] Need to make a GUI? You NEED KODA FormDesigner!

Share this post


Link to post
Share on other sites

You can switch ON and Off the visibility of the window so that is no problem - check WinSetState ( "title", "text", flag )

The excel file you can read with _ExcelReadArray that returns array or _ExcelReadCell. Check the excel UDF.

Share this post


Link to post
Share on other sites

The below script assumes that the first row of data is a header and that column a is the name and column b is the number.

#include <IE.au3>
#include <Excel.au3>
#include <Array.au3>
#include <GuiListView.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>

#AutoIt3Wrapper_Au3Check_Parameters = -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

Opt("GUIOnEventMode", True)

Global $Form1, $Input1, $Button1, $ListView1, $sFilePath, $oExcel

$Form1 = GUICreate("Phone Number Search", 633, 447, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "_Exit")
$Input1 = GUICtrlCreateInput("Type search here...", 5, 7, 451, 21)
;~ GUICtrlSetOnEvent(-1, "_Clear")
$Button1 = GUICtrlCreateButton("Search", 465, 5, 160, 25, BitOR($BS_DEFPUSHBUTTON, $WS_GROUP))
GUICtrlSetOnEvent(-1, "_Search")
$ListView1 = GUICtrlCreateListView("Name|Number", 5, 35, 620, 405, BitOR($LVS_REPORT, $LVS_SINGLESEL, $LVS_SHOWSELALWAYS, $LVS_SORTASCENDING, $LVS_AUTOARRANGE), BitOR($WS_EX_CLIENTEDGE, $LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT))
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 250)
GUISetState(@SW_SHOW)

_GUICtrlListView_SetColumnWidth($ListView1, 1, $LVSCW_AUTOSIZE_USEHEADER)

_IEErrorHandlerRegister()
$sFilePath = "C:\Test\Excel.xlsx"
$oExcel = _ExcelBookOpen($sFilePath, False)
If @error Then
    MsgBox(64, "Error", "Failed opening Excel workbook.")
    _Exit()
EndIf

While 1
    Sleep(100)
WEnd

Func _Search()
    _GUICtrlListView_DeleteAllItems($ListView1)
    Local $sName = GUICtrlRead($Input1)
    Local $aSheet = _ExcelReadSheetToArray($oExcel, 2, 1)
    If @error Then
        MsgBox(64, "Error", "Failed reading sheet to array")
        Return 0
    EndIf

    Local $aIndexes = _ArrayFindAll($aSheet, $sName, 0, 0, 0, 1, 1)
    If @error Then Return 0

    For $i = 0 To UBound($aIndexes) - 1
        GUICtrlCreateListViewItem($aSheet[$aIndexes[$i]][1] & "|" & $aSheet[$aIndexes[$i]][2], $ListView1)
    Next
EndFunc   ;==>_Search

Func _Exit()
    GUISetState(@SW_HIDE)
    If IsObj($oExcel) Then _ExcelBookClose($oExcel)
    Exit
EndFunc   ;==>_Exit

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