Soft that search phone number in excel

[iks2]

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

[KenNichols]

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

[Juvigy]

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.

[big_daddy]

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.

expandcollapse popup

#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