There are around 10 thousand account numbers and names in this workbook. I want that when i press numpadenter in workbookB, then it should first search the value (account number) of currently selected cell into workbookA and if it finds the account number in workbookA then it should retrieve the name of that account number into workbookB.
WorkbookB is something like this-
My codes are these-
However the above codes are working fine to some extent, but they are little bit slow and sometimes they stop working even. i have to press enter again and again that’s why i think they lag in performance. I want to know that is it a good way to retrieve values from an excel database like above? Or it is good way to create a database of account numbers and names into excel like the above? There is something else which can i do to increase its performance? Please help me. Thanks a lot..
Code: Select all
#IfWinActive Microsoft Excel - workbookB.xlsx ; i created context sensitive hotkey as these codes should run only in workbookB. ~NumpadEnter:: xl:= ComObjActive("Excel.Application") xlApp := ComObjCreate("Excel.Application") xl1 := xlApp.Workbooks.Open("F:\workdbookA.xlsx") ; the workbook in which account numbers should be searched tt:= xl.activecell.offset(-1,0).value xl.activecell.offset(-1,1).value:= xl1.sheets("sheet1").range("a1:a10000").Find(tt,,,lookat:=1).offset(0,1).value xlapp.application.quit return #IfWinActive