How to call a vlookup from AutoHotkey? Topic is solved

Get help with using AutoHotkey (v2 or newer) and its commands and hotkeys
Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

How to call a vlookup from AutoHotkey?

Post by Avastgard » 14 Mar 2024, 15:45

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?
Last edited by Avastgard on 14 Mar 2024, 20:36, edited 1 time in total.

User avatar
flyingDman
Posts: 2847
Joined: 29 Sep 2013, 19:01

Re: How to call a vlookup from AutoHotkey?

Post by flyingDman » 14 Mar 2024, 18:11

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).
14.3 & 1.3.7

User avatar
boiler
Posts: 17384
Joined: 21 Dec 2014, 02:44

Re: How to call a vlookup from AutoHotkey?

Post by boiler » 14 Mar 2024, 19:33

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
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 14 Mar 2024, 20:41

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?

User avatar
boiler
Posts: 17384
Joined: 21 Dec 2014, 02:44

Re: How to call a vlookup from AutoHotkey?

Post by boiler » 14 Mar 2024, 20:47

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?

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 14 Mar 2024, 20:56

Do you want me to move this thread to the v2 section?
I would appreciate that. Thank you.

User avatar
flyingDman
Posts: 2847
Joined: 29 Sep 2013, 19:01

Re: How to call a vlookup from AutoHotkey?

Post by flyingDman » 14 Mar 2024, 23:13

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:

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
v2:

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
BTW I typically avoid Vlookup and either use something like:

Code: Select all

for c in xl.range("a1:b23")
	if (c.value = needle)
		msgbox % c.offset(0,1).value
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.
14.3 & 1.3.7

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 15 Mar 2024, 08:15

Thanks for the code, @flyingDman.

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
Also, how do I specify which sheet should be looked up?

RussF
Posts: 1311
Joined: 05 Aug 2021, 06:36

Re: How to call a vlookup from AutoHotkey?

Post by RussF » 15 Mar 2024, 09:07

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

User avatar
flyingDman
Posts: 2847
Joined: 29 Sep 2013, 19:01

Re: How to call a vlookup from AutoHotkey?

Post by flyingDman » 15 Mar 2024, 14:28

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:

Code: Select all

xl.worksheets("sheet2").range("a1:b23")
Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AY
Thanks @RussF for testing!
14.3 & 1.3.7

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 18 Mar 2024, 12:42

RussF wrote:
15 Mar 2024, 09:07
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
Thank you for the tips, @RussF. I made a sheet from scratch and the code provided by @flyingDman worked.
flyingDman wrote:
15 Mar 2024, 14:28
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:

Code: Select all

xl.worksheets("sheet2").range("a1:b23")
Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AY
Thanks @RussF for testing!
@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.

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
However, I realized that this won't work for me because I need an easy way to input the lookup value (column A) and have the output value (column B) show up on a MsgBox or be pasted on the text. I tried adding a hotkey to trigger an InputBox to store the input as a variable, but this is a little too advanced for me and v2 syntax is killing me. How can I dynamically replace the value of needle with user input?

User avatar
flyingDman
Posts: 2847
Joined: 29 Sep 2013, 19:01

Re: How to call a vlookup from AutoHotkey?  Topic is solved

Post by flyingDman » 18 Mar 2024, 16:42

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

RussF
Posts: 1311
Joined: 05 Aug 2021, 06:36

Re: How to call a vlookup from AutoHotkey?

Post by RussF » 19 Mar 2024, 06:38

@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

User avatar
boiler
Posts: 17384
Joined: 21 Dec 2014, 02:44

Re: How to call a vlookup from AutoHotkey?

Post by boiler » 19 Mar 2024, 06:49

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.

RussF
Posts: 1311
Joined: 05 Aug 2021, 06:36

Re: How to call a vlookup from AutoHotkey?

Post by RussF » 19 Mar 2024, 07:41

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

Russ

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 19 Mar 2024, 08:52

flyingDman wrote:
18 Mar 2024, 16:42
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
	}
Awesome, thank you very much, @flyingDman.

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 19 Mar 2024, 08:52

flyingDman wrote:
18 Mar 2024, 16:42
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
	}
Awesome, thank you very much, @flyingDman.

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.

RussF
Posts: 1311
Joined: 05 Aug 2021, 06:36

Re: How to call a vlookup from AutoHotkey?

Post by RussF » 19 Mar 2024, 09:09

In V2, the clipboard name is A_Clipboard

Russ

Avastgard
Posts: 133
Joined: 30 Sep 2016, 21:54

Re: How to call a vlookup from AutoHotkey?

Post by Avastgard » 19 Mar 2024, 09:16

RussF wrote:
19 Mar 2024, 09:09
In V2, the clipboard name is A_Clipboard

Russ
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
	}

Post Reply

Return to “Ask for Help (v2)”