How to call a vlookup from AutoHotkey? Topic is solved
How to call a vlookup from AutoHotkey?
I want to use an Excel spreadsheet to structure data that will be retrieved and pasted by an AutoHotkey script.
In other words, I want to tell an AutoHotkey script to take a certain value given by the user (maybe typing it in an inputbox or GUI), look it up in a spreadsheet and return the value in the same row 2 columns over to the right (in a MsgBox or pasting it in the text), all of this without opening the spreadsheet. Basically a VLookup.
I know how to do this without using the sheet, but in my use case, other people will update the database in the sheet and having the script retrieve the info from there would be ideal instead of people messing with the script.
Edit: Any tips on where to start with this?
In other words, I want to tell an AutoHotkey script to take a certain value given by the user (maybe typing it in an inputbox or GUI), look it up in a spreadsheet and return the value in the same row 2 columns over to the right (in a MsgBox or pasting it in the text), all of this without opening the spreadsheet. Basically a VLookup.
I know how to do this without using the sheet, but in my use case, other people will update the database in the sheet and having the script retrieve the info from there would be ideal instead of people messing with the script.
Edit: Any tips on where to start with this?
Last edited by Avastgard on 14 Mar 2024, 20:36, edited 1 time in total.
- flyingDman
- Posts: 2848
- Joined: 29 Sep 2013, 19:01
Re: How to call a vlookup from AutoHotkey?
try with rangelookup := 0
BTW your posting in the wrong section (this is v1 code) and you're yet again confirming that AI generated code is not up to snuff yet(and against forum rules).
BTW your posting in the wrong section (this is v1 code) and you're yet again confirming that AI generated code is not up to snuff yet(and against forum rules).
14.3 & 1.3.7
Re: How to call a vlookup from AutoHotkey?
Moved topic from the v2 section.
@Avastgard - Please don't post AI-generated code in order to get help correcting it as that is against the forum rules.
@Avastgard - Please don't post AI-generated code in order to get help correcting it as that is against the forum rules.
Re: How to call a vlookup from AutoHotkey?
Oops, sorry guys. I remember reading this rule a while ago, but completely forgot about it. I deleted the AI-generated code from my question.
@boiler I honestly have no idea whether v1 or v2 are better suited for this use case. Now that the post has no code does it make a difference where it is?
@boiler I honestly have no idea whether v1 or v2 are better suited for this use case. Now that the post has no code does it make a difference where it is?
Re: How to call a vlookup from AutoHotkey?
You should post in the section for whichever version you want to use so helpers know which version to use in their replies. Neither one is better suited except in cases where a library exists for one but not the other, which wouldn't be the case here. If you have no other reason, always choose v2. Do you want me to move this thread to the v2 section?
Re: How to call a vlookup from AutoHotkey?
I would appreciate that. Thank you.Do you want me to move this thread to the v2 section?
- flyingDman
- Posts: 2848
- Joined: 29 Sep 2013, 19:01
Re: How to call a vlookup from AutoHotkey?
This brings out an interesting difference between V1 and V2. Using range_lookup := false in v1 triggers the 0x800A03EC error while range_lookup := 0 does not (and finds the expected result) while in v2 both false and 0 are fine.
v1:
v2:
BTW I typically avoid Vlookup and either use something like:
Which finds multiple results (if there are more than 1) or in case of a large spreadsheet. I will save the whole range to a safearray and search the safearray. The latter is much faster.
v1:
Code: Select all
needle := "matrix"
xl := ComObjCreate("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\xxx\Documents\Spreadsheets\vlookuptest.xlsx")
range_lookup := 0 ; := false is not OK
msgbox % Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup)
Workbook.close(0)
xl.quit
Code: Select all
needle := "matrix"
xl := ComObject("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\xxx\Documents\Spreadsheets\vlookuptest.xlsx")
range_lookup := 0 ; := false is OK
msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup)
Workbook.close(0)
xl.quit
Code: Select all
for c in xl.range("a1:b23")
if (c.value = needle)
msgbox % c.offset(0,1).value
14.3 & 1.3.7
Re: How to call a vlookup from AutoHotkey?
Thanks for the code, @flyingDman.
However, I run into the same error message with the v2 version:
Also, how do I specify which sheet should be looked up?
However, I run into the same error message with the v2 version:
Code: Select all
Error: (0x800A03EC)
Unable to obtain VLookup property from class WorksheetFunction
Source: Microsoft Excel
Specifically: VLookup
005: Workbook := xl.Workbooks.Open("C:\Users\flima\Desktop\lista CNPJs KBI.xlsx")
006: range_lookup := 0
▶ 007: msgbox(Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup))
008: Workbook.close(0)
009: xl.quit()
Call stack:
C:\Users\flima\AutoHotkey\Excel\Excel paste vlookup.ahk (4) : [] xl := ComObject("Excel.Application")
> Auto-execute
Re: How to call a vlookup from AutoHotkey?
FWIW, I created a spreadsheet that matches the guidelines in @flyingDman's V2 example: name - vlookuptest.xlsx, 2 columns, 23 rows, cells in column A were junk text except for one entry "matrix" and just sequential integers in column B. I also changed the path in his code to point to my file.
As long as all the criteria were as listed above, it worked perfectly. If I changed the range, or changed "matrix" to something else, or pointed to a nonexistent file - basically anything that would case the lookup to fail, I got the same error code, albeit with different text description.
Make sure everything matches correctly. You may want to try his second alternative as well - less prone to throwing Excel errors.
Russ
As long as all the criteria were as listed above, it worked perfectly. If I changed the range, or changed "matrix" to something else, or pointed to a nonexistent file - basically anything that would case the lookup to fail, I got the same error code, albeit with different text description.
Make sure everything matches correctly. You may want to try his second alternative as well - less prone to throwing Excel errors.
Russ
- flyingDman
- Posts: 2848
- Joined: 29 Sep 2013, 19:01
Re: How to call a vlookup from AutoHotkey?
Google error 0x800A03EC and see what could be the issue. There are a number of potential problems but they are likely related to Excel and Windows rather than AHK. Have you been able to run other scripts with COM for Excel?
To access a different sheet use this:
Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AY
Thanks @RussF for testing!
To access a different sheet use this:
Code: Select all
xl.worksheets("sheet2").range("a1:b23")
Thanks @RussF for testing!
14.3 & 1.3.7
Re: How to call a vlookup from AutoHotkey?
Thank you for the tips, @RussF. I made a sheet from scratch and the code provided by @flyingDman worked.RussF wrote: ↑15 Mar 2024, 09:07FWIW, I created a spreadsheet that matches the guidelines in @flyingDman's V2 example: name - vlookuptest.xlsx, 2 columns, 23 rows, cells in column A were junk text except for one entry "matrix" and just sequential integers in column B. I also changed the path in his code to point to my file.
As long as all the criteria were as listed above, it worked perfectly. If I changed the range, or changed "matrix" to something else, or pointed to a nonexistent file - basically anything that would case the lookup to fail, I got the same error code, albeit with different text description.
Make sure everything matches correctly. You may want to try his second alternative as well - less prone to throwing Excel errors.
Russ
@flyingDman, thank you for the very informative video. I was actually aware that there are better alternatives to VLookup, like Index Match and XLookup, but I figured I would get more responses if asked about VLookup.flyingDman wrote: ↑15 Mar 2024, 14:28Google error 0x800A03EC and see what could be the issue. There are a number of potential problems but they are likely related to Excel and Windows rather than AHK. Have you been able to run other scripts with COM for Excel?
To access a different sheet use this:Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AYCode: Select all
xl.worksheets("sheet2").range("a1:b23")
Thanks @RussF for testing!
The code below works on a brand new spreadsheet:
Code: Select all
#Requires Autohotkey v2.0+
#SingleInstance force
needle := "matrix"
xl := ComObject("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\username\AutoHotkey\Excel\vlookuptest.xlsx")
xl.worksheets("test").range("a:b")
range_lookup := 0 ; := false is OK
msgbox xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
Workbook.close(0)
xl.quit
- flyingDman
- Posts: 2848
- Joined: 29 Sep 2013, 19:01
Re: How to call a vlookup from AutoHotkey? Topic is solved
Inputbox with F12 as hotkey:
Code: Select all
f12::
{
needle := InputBox("Lookup value?", "")
needle := needle.value ;instead of needle := "matrix"
xl := ComObject("Excel.Application") ;v1 ---> xl := ComObjCreate("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx")
range_lookup := 0
msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
Workbook.close(0)
xl.quit
}
14.3 & 1.3.7
Re: How to call a vlookup from AutoHotkey?
@flyingDman, you name both the InputBox object and the result string "needle". The next (and only) reference to "needle" is to its string form, which is the last thing you assigned to it. I'm just curious, if you needed to refer back to the object, would AHK be able to differentiate between the two by context?
I never give two different variable types the same name just to avoid such ambiguities.
Russ
I never give two different variable types the same name just to avoid such ambiguities.
Russ
Re: How to call a vlookup from AutoHotkey?
The object doesn’t exist anymore after he replaces it with the value property’s contents (a string), so there’s no ambiguity. The variable needle is only a string with the user’s input after that point, which is what he wants. He is eliminating the object because he knows he is not going to make use of the other property of the object — the button that was clicked.
Re: How to call a vlookup from AutoHotkey?
Thanks @boiler, after all this time using AHK, I still have the "avoid the type mismatch error" syndrome, even though AHK has no problem with it.
Russ
Russ
Re: How to call a vlookup from AutoHotkey?
Awesome, thank you very much, @flyingDman.flyingDman wrote: ↑18 Mar 2024, 16:42Inputbox with F12 as hotkey:Code: Select all
f12:: { needle := InputBox("Lookup value?", "") needle := needle.value ;instead of needle := "matrix" xl := ComObject("Excel.Application") ;v1 ---> xl := ComObjCreate("Excel.Application") Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx") range_lookup := 0 msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) Workbook.close(0) xl.quit }
Re: How to call a vlookup from AutoHotkey?
Awesome, thank you very much, @flyingDman.flyingDman wrote: ↑18 Mar 2024, 16:42Inputbox with F12 as hotkey:Code: Select all
f12:: { needle := InputBox("Lookup value?", "") needle := needle.value ;instead of needle := "matrix" xl := ComObject("Excel.Application") ;v1 ---> xl := ComObjCreate("Excel.Application") Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx") range_lookup := 0 msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) Workbook.close(0) xl.quit }
Would it be possible to send the result of line 8's Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) to clipboard and then paste it? I tried Clipboard := Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup), but it just literaly pastes Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) on the text.
Re: How to call a vlookup from AutoHotkey?
In V2, the clipboard name is A_Clipboard
Russ
Russ
Re: How to call a vlookup from AutoHotkey?
Thanks again! Here is the final code that will paste the VLookup result instead of displaying it on a MsgBox and allows you to choose the sheet to lookup:
Code: Select all
#Requires Autohotkey v2.0+
#SingleInstance force
F10::
{
needle := InputBox("Type Lookup value", "", "w200 h100")
needle := needle.value ;instead of needle := "matrix"
xl := ComObject("Excel.Application") ;v1 ---> xl := ComObjCreate("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\username\AutoHotkey\Excel\spreadsheet.xlsx")
xl.worksheets("Sheet1").range("a:b")
range_lookup := 0
A_Clipboard := Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
Workbook.close(0)
xl.quit
Send "^v"
return
}