Seeking help for finding and retrieving values from Excel Workbooks using com object module?

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Sabestian Caine
Posts: 279
Joined: 12 Apr 2015, 03:53

Seeking help for finding and retrieving values from Excel Workbooks using com object module?

09 Feb 2018, 13:07

Friends, i want to find and retrieve values from one excel workbook to another workbook. Say i have two workbooks- workbookA, which contains account numbers and their names and workbookB, in which i want to insert the retrieved values from workbookA. The workbookA from which i want to find and retrieve values looks like this-



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-

Code: Select all

#IfWinActive Microsoft Excel - workbookB.xlsx ; i created context sensitive hotkey as these codes should run only in workbookB.
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
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..
I don't normally code as I don't code normally.
YOU do what YOU want, but happens what HE wants.
YOU do what HE wants, will happen what YOU want.

Return to “Ask For Help”

Who is online

Users browsing this forum: loek3000, Scr1pter, scriptors and 62 guests