Excel Search

[chrisgreece]

I spent today looking through Excel Range Find and  other ways so that i can find matches from Excel B to Excel A and change some values

Although _Excel_range_find didnt help me so much and other methods were quite slow, i went ahead and took some posts from here and there,

from this forum, and ended up with a really fast Search and Write code.

If you have any ideas how to do it better please share!

I think it is really fast and also convenient

I use it for adding the work hours in our employees

expandcollapse popup

#include <Excel.au3>
#include <MsgboxConstants.au3>
#include <array.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GUIDateTimePicker.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GUIDateTimePicker.au3>
#include <Array.au3>
#include <String.au3>



Global $Paused

HotKeySet ("{PAUSE}", "TogglePause")
HotKeySet ("{F12}", "Terminate")



Example ()


Func TogglePause()
   $Paused = Not $paused
   While $Paused
   WEnd
EndFunc

Func Terminate ()
   Exit

   EndFunc
Func _WinWaitActivate($title,$text,$timeout=0)
    WinWait($title,$text,$timeout)
    If Not WinActive($title,$text) Then WinActivate($title,$text)
    WinWaitActive($title,$text,$timeout)
 EndFunc

Func Example()


   $Form1 = GUICreate("EXCEL FINDER AND REPLACER", 500, 200, 210, 124)
      GUICtrlCreatePic ("C:\Documents and Settings\chris\Τα έγγραφά μου\Οι εικόνες μου\Wallpapers\4.jpg",0,0,500,200)
      GUICtrlSetState (-1, $GUI_DISABLE)
      $r1 = GUICtrlCreateInput ("(Α)", 10, 30,80,27, $WS_GROUP) ;This reads from E and the number you will put in
      $r = GUICtrlCreateInput ("(Β)", 90, 30,80,27, $WS_GROUP) ;Till F and the number you will also put here (you can Change it below)
      $BTN = GUICtrlCreateButton("GO", 10, 120, 160, 41, $WS_GROUP)
      GuiSetState (@sw_show)

            While 1
               Switch GUIGetMsg()
               Case $GUI_EVENT_CLOSE
            ExitLoop


Case $BTN ;Hit that to start

         Local $oExcel =_Excel_Open(False)

               ;FIRST EXCEL
               Local $bWorkbook = _Excel_BookOpen($oExcel, "C:\Users\chris\Desktop\test3.xlsx") ;This is the Excel i will look into and also put some values

               Global $BExcel =_Excel_BookOpen($oExcel,$bWorkbook)

               $bWorkbook.sheets(1).Select

               $LastRow = 325 ;I Set it to 325 because i already know how many rows i have, otherwise you can use "$bWorkbook.ActiveSheet.UsedRange.Rows.Count"

               $bResults = _Excel_RangeRead($bWorkbook, Default, "A1:B" & $LastRow)
               ;END OF FIRST EXCEL


               ;SECOND EXCEL
               Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Users\chris\Desktop\test.xlsm") ; Here are the values that i want to look for in the First Excel

               Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E"&GUICtrlRead($r1)& ":F"  & (GUICtrlRead($r)), 1) ;HEre it reads the numbers we put on the gui for example from E2 to F255
               ;End Of First Excel



For $r=0 TO UBound($aResult,1)-1 ; First we are going to loop through all the values from the second Excel that has all the information we are looking for.

      For $LastRow=0 To UBound($bResults,1)-1 ;This loop is actually searching through the first Excel to locate the values we are looking for ( From the Second Excel)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;$bResults[$lastRow][0] are the names we are looking through and we get those values from $aResult[$r][0])


                  If StringinStr($bResults[$lastRow][0],$aResult[$r][0])   Then ; If we find a match then we can add a value ; Here i am looking for employees so i can set how many hours they have worked

                  $BAD=_Excel_RangeRead($bWorkbook, Default,"B" & $lastRow )

                  $BAD=MsgBox(0, '',$bResults[$lastRow][0] & "found " & $aResult[$r][0] ) ; Just a msgbox to make sure it is the person i am going to add the Worktime

                  _Excel_RangeWrite($bWorkbook, Default, $aResult[$r][1], "B" & $lastRow,True,True ) ; $aResult[1]  This is where i had stored the time in the second Excel and now moving it to the first

                  ExitLoop ; Exiting the loop for that person because we already put the worktime

                  Else

                  Endif

      Next ; Here we ll go back to search for the second person from ExcelB to Excel A



Next ; Here it ends After having searched for all the values in ExcelB
MsgBox(1,"completed","done")
_Excel_Close($oExcel)

_Excel_Close($bExcel)

Exit
EndSwitch
WEnd
EndFunc

 

[water]

Here some comments:

• Local $oExcel =_Excel_Open(False)  ; << Don't do this in the loop. Open Excel once when the script gets started
• Global $BExcel =_Excel_BookOpen($oExcel,$bWorkbook) ; << Drop this statement. Doesn't make sense
• $BAD=_Excel_RangeRead($bWorkbook, Default,"B" & $lastRow ) ; << Reading Excel cells in a loop isn't very efficient. Keep the row number you need to write to in a variable
• _Excel_RangeWrite($bWorkbook, Default, $aResult[$r][1], "B" & $lastRow,True,True) ; << Same as above. Keep the values in an array and write the array at the end of the script